讀SQL進階教程筆記06_外連接

来源:https://www.cnblogs.com/lying7/archive/2023/04/03/17281562.html
-Advertisement-
Play Games

1. SQL的弱點 1.1. SQL語句的執行結果轉換為想要的格式 1.1.1. 格式轉換 1.1.2. SQL語言本來就不是為了這個目的而出現的 1.1.3. SQL終究也只是主要用於查詢數據的語言而已 1.2. 生成報表的功能 1.2.1. 視窗函數 1.3. SQL不是用來生成報表的語言，所以 ...


1. SQL的弱點

1.1. SQL語句的執行結果轉換為想要的格式

  • 1.1.1. 格式轉換

  • 1.1.2. SQL語言本來就不是為了這個目的而出現的

  • 1.1.3. SQL終究也只是主要用於查詢數據的語言而已

1.2. 生成報表的功能

  • 1.2.1. 視窗函數

1.3. SQL不是用來生成報表的語言，所以不建議用它來進行格式轉換

  • 1.3.1. 必要時考慮用外連接或CASE表達式來解決問題

2. 製作交叉表（行→列）

2.1. 示例

  • 2.1.1.

  • 2.1.1.1. --水平展開求交叉表(1)：使用外連接
   SELECT C0.name,
     CASE WHEN C1.name IS NOT NULL THEN'○'ELSE NULL END AS "SQL入門",
     CASE WHEN C2.name IS NOT NULL THEN'○'ELSE NULL END AS "UNIX基礎",
     CASE WHEN C3.name IS NOT NULL THEN'○'ELSE NULL END AS "Java中級"
     FROM  (SELECT DISTINCT name FROM  Courses) C0   --這裡的C0是側欄
     LEFT OUTER JOIN
       (SELECT name FROM Courses WHERE course = 'SQL入門’) C1
       ON  C0.name = C1.name
         LEFT OUTER JOIN
           (SELECT name FROM Courses WHERE course = 'UNIX基礎’) C2
           ON  C0.name = C2.name
       LEFT OUTER JOIN
         (SELECT name FROM Courses WHERE course = 'Java中級’) C3
         ON  C0.name = C3.name;

  • 2.1.1.2. 一般情況下，外連接都可以用標量子查詢替代

    2.1.1.2.1. 需要增加或者減少課程時，只修改SELECT子句即可，代碼修改起來比較簡單

    2.1.1.2.2. 利於應對需求變更，對於需要動態生成SQL的系統也是很有好處的

    2.1.1.2.3. 性能不太好

  • 2.1.1.3. --水平展開(2)：使用標量子查詢

   SELECT C0.name,
         (SELECT '○'
             FROM Courses C1
           WHERE course = 'SQL入門’
             AND C1.name = C0.name) AS "SQL入門",
         (SELECT '○'
             FROM Courses C2
           WHERE course = 'UNIX基礎’
               AND C2.name = C0.name) AS "UNIX基礎",
           (SELECT '○'
              FROM Courses C3
             WHERE course = 'Java中級’
               AND C3.name = C0.name) AS "Java中級"
       FROM (SELECT DISTINCT name FROM Courses) C0;  --這裡的C0是表側欄

  • 2.1.1.4. 嵌套使用CASE表達式

    2.1.1.4.1. CASE表達式可以寫在SELECT子句里的聚合函數內部，也可以寫在聚合函數外部

    2.1.1.4.2. 其實在SELECT子句里，聚合函數的執行結果也是標量值，因此可以像常量和普通列一樣使用

    2.1.1.4.3. 和標量子查詢的做法一樣簡潔，也能靈活地應對需求變更

  • 2.1.1.5. --水平展開(3)：嵌套使用CASE表達式

   SELECT name,
     CASE WHEN SUM(CASE WHEN course = 'SQL入門’THEN 1 ELSE NULL END) = 1
         THEN'○'ELSE NULL END AS "SQL入門",
     CASE WHEN SUM(CASE WHEN course = 'UNIX基礎’THEN 1 ELSE NULL END) = 1
         THEN'○'ELSE NULL END AS "UNIX基礎",
     CASE WHEN SUM(CASE WHEN course = 'Java中級’THEN 1 ELSE NULL END) = 1
         THEN'○'ELSE NULL END AS "Java中級"
     FROM Courses
    GROUP BY name;

3. 彙總重覆項於一列（列→行）

3.1. 示例

  • 3.1.1.

  • 3.1.1.1. --列數據轉換成行數據：使用UNION ALL
   SELECT employee, child_1 AS child FROM Personnel
   UNION ALL
   SELECT employee, child_2 AS child FROM Personnel
   UNION ALL
   SELECT employee, child_3 AS child FROM Personnel;
  • 3.1.1.2.  視圖
CREATE VIEW Children(child)
  AS SELECT child_1 FROM Personnel
    UNION
    SELECT child_2 FROM Personnel
    UNION
    SELECT child_3 FROM Personnel;
  child
-----
一郎
二郎
三郎
春子
夏子

3.1.1.2.1. --獲取員工子女列表的SQL語句（沒有孩子的員工也要輸出）

   SELECT EMP.employee, CHILDREN.child
     FROM Personnel EMP
         LEFT OUTER JOIN Children
           ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);

4. 製作嵌套式表側欄

4.1. 示例

  • 4.1.1.


  • 4.1.2.

  • 4.1.3. 結果

    • 4.1.3.1.

  • 4.1.4. --使用外連接生成嵌套式表側欄：錯誤的SQL語句
SELECT  MASTER1.age_class AS age_class,
        MASTER2.sex_cd    AS sex_cd,
        DATA.pop_tohoku   AS pop_tohoku,
        DATA.pop_kanto    AS pop_kanto
  FROM (SELECT age_class, sex_cd,
            SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
                      THEN population ELSE NULL END) AS pop_tohoku,
            SUM(CASE WHEN pref_name IN (’東京’, ’千葉’)
                      THEN population ELSE NULL END) AS pop_kanto
          FROM TblPop
        GROUP BY age_class, sex_cd) DATA
        RIGHT OUTER JOIN TblAge MASTER1--外連接1：和年齡層級主表進行外連接
            ON MASTER1.age_class = DATA.age_class
        RIGHT OUTER JOIN TblSex MASTER2--外連接2：和性別主表進行外連接
            ON MASTER2.sex_cd = DATA.sex_cd;
  • 4.1.4.1. --停在第1個外連接處時：結果里包含年齡層級為2的數據
SELECT MASTER1.age_class AS age_class,
      DATA.sex_cd        AS sex_cd,
      DATA.pop_tohoku   AS pop_tohoku,
      DATA.pop_kanto    AS pop_kanto
  FROM (SELECT age_class, sex_cd,
            SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
                      THEN population ELSE NULL END) AS pop_tohoku,
            SUM(CASE WHEN pref_name IN (’東京’, ’千葉’)
                      THEN population ELSE NULL END) AS pop_kanto
            FROM TblPop
           GROUP BY age_class, sex_cd) DATA
           RIGHT OUTER JOIN TblAge MASTER1
              ON MASTER1.age_class = DATA.age_class;

  • 4.1.5. 如果不允許進行兩次外連接，那麼調整成一次就可以了

  • 4.1.6. 對於不支持CROSS JOIN語句的資料庫，可以像FROM TblAge,TblSex這樣不指定連接條件，把需要連接的表寫在一起，其效果與交叉連接一樣

  • 4.1.7. 如果先生成主表的笛卡兒積再進行連接，很容易就可以完成

  • 4.1.8. --使用外連接生成嵌套式表側欄：正確的SQL語句

   SELECT MASTER.age_class AS age_class,
         MASTER.sex_cd    AS sex_cd,
         DATA.pop_tohoku  AS pop_tohoku,
         DATA.pop_kanto   AS pop_kanto
    FROM (SELECT age_class, sex_cd
           FROM TblAge CROSS JOIN TblSex ) MASTER  --使用交叉連接生成兩張主表的笛卡兒積
       LEFT OUTER JOIN
         (SELECT age_class, sex_cd,
               SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
                       THEN population ELSE NULL END) AS pop_tohoku,
               SUM(CASE WHEN pref_name IN (’東京’, ’千葉’)
                       THEN population ELSE NULL END) AS pop_kanto
           FROM TblPop
           GROUP BY age_class, sex_cd) DATA
             ON  MASTER.age_class = DATA.age_class
           AND  MASTER.sex_cd    = DATA.sex_cd;

5. 作為乘法運算的連接

5.1. 示例

  • 5.1.1.


  • 5.1.2. --解答(1)：通過在連接前聚合來創建一對一的關係
   SELECT I.item_no, SH.total_qty
     FROM Items I LEFT OUTER JOIN
           (SELECT item_no, SUM(quantity) AS total_qty
               FROM SalesHistory
             GROUP BY item_no) SH
       ON I.item_no = SH.item_no;

  • 5.1.2.1. 以商品編號為主鍵的臨時視圖

  • 5.1.2.2. 無法利用索引優化查詢

  • 5.1.3. --解答(2)：先進行一對多的連接再聚合

   SELECT I.item_no, SUM(SH.quantity) AS total_qty
     FROM Items I LEFT OUTER JOIN SalesHistory SH
       ON I.item_no = SH.item_no  一對多的連接
    GROUP BY I.item_no;

  • 5.1.3.1. 代碼更簡潔

  • 5.1.3.2. 沒有使用臨時視圖，所以性能也會有所改善

5.2. 從行數來看，表連接可以看成乘法。因此，當表之間是一對多的關係時，連接後行數不會增加

6. 全外連接

6.1. FULL OUTER JOIN

6.2. 相當於求集合的和（UNION，也稱並集）

  • 6.2.1. 內連接相當於求集合的積（INTERSECT，也稱交集）

6.3. 示例

  • 6.3.1. --全外連接保留全部信息
   SELECT COALESCE(A.id, B.id) AS id,
         A.name AS A_name,
         B.name AS B_name
     FROM Class_A  A  FULL OUTER JOIN Class_B  B
       ON A.id = B.id;
  • 6.3.1.1. --資料庫不支持全外連接時的替代方案
   SELECT A.id AS id, A.name, B.name
     FROM Class_A  A   LEFT OUTER JOIN Class_B  B
       ON A.id = B.id
   UNION
   SELECT B.id AS id, A.name, B.name
     FROM Class_A  A  RIGHT OUTER JOIN Class_B  B
       ON A.id = B.id;

6.3.1.1.1. 分別進行左外連接和右外連接，再把兩個結果通過UNION合併起來

6.4. COALESCE是SQL的標準函數

  • 6.4.1. 可以接受多個參數，功能是返回第一個非NULL的參數

6.5. 外連接的思想和集合運算很像，使用外連接可以實現各種集合運算

7. 用外連接求差集：B－A

7.1. 示例

  • 7.1.1.
SELECT B.id AS id, B.name AS B_name

     FROM Class_A  A  RIGHT OUTER JOIN Class_B B
       ON A.id = B.id
    WHERE A.name IS NULL;

7.2. 可以作為NOT IN和NOT EXISTS之外的另一種解法

7.3. 可能是差集運算中效率最高的

8. 用全外連接求異或集

8.1. SQL沒有定義求異或集的運算符

8.2. 用集合運算符

  • 8.2.1. (A UNION B) EXCEPT (A INTERSECT B)

  • 8.2.2. (A EXCEPT B) UNION (B EXCEPT A)

  • 8.2.3. 性能開銷大

8.3. 示例

  • 8.3.1.
SELECT COALESCE(A.id, B.id) AS id,
         COALESCE(A.name , B.name ) AS name
     FROM Class_A  A  FULL OUTER JOIN Class_B  B
       ON A.id = B.id
    WHERE A.name IS NULL
       OR B.name IS NULL;

9. 用外連接進行關係除法

9.1. 示例

  • 9.1.1. --用外連接進行關係除法運算：差集的應用
   SELECT DISTINCT shop
     FROM ShopItems SI1
   WHERE NOT EXISTS
         (SELECT I.item
           FROM Items I LEFT OUTER JOIN ShopItems SI2
             ON I.item   = SI2.item
             AND SI1.shop = SI2.shop
           WHERE SI2.item IS NULL) ;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 基於ZYNQ的OV5640攝像頭的sobel運算元邊緣檢測
    最近鴿了挺久的，因為最近要做課設，再加上被這個工程的調試給難到了。 在做該工程的時候，有一個良好的項目管理習慣會讓開發的時候不會讓人那麼的高血壓。 特別要註意的是，非同步FIFO的讀寫時鐘的速率匹配問題，這個問題卡了我好久。 1、sobel運算元 Sobel 演算法是像素圖像邊緣檢測中最重要的運算元之一，在 ...
  • Anaconda 使用筆記 （可能有些亂 自己挑著看）
    查看鏡像 conda config --show channels 刪除鏡像 conda config --remove channels 源名稱或鏈接 恢復預設鏡像 conda config --remove-key channels 添加鏡像 清華鏡像更新了 conda config --add ...
  • .net core基於HttpClient實現的網路請求庫
    Soda.Http 基於HttpClient封裝的 Http 請求庫。如果有什麼好想法，可以提 Issue 或者 Pr。，如果想要使用，直接在nuget搜索Soda.Http即可。 Github項目地址：Soda.Http 用法 1 預載 預載配置並不是必須的，但是有助於我們進行一些通用基礎設置，例 ...
  • abp(net core)+easyui+efcore實現倉儲管理系統——ABP升級7.3下(五十九)
    1. 微軟擴展官網 微軟擴展官網：—>Visual Studio Marketplace 2. 待安裝的擴展包 | 序號 | 擴展包(vsix包) | 官方說明 | | | | | | 1 | ClaudiaIDE | 此擴展更改編輯器的背景圖像 | | 2 | Visual Studio Colo ...
  • 明解STM32—GPIO理論基礎知識篇之寄存器原理
    ​ 一、前言 在之前的STM32的GPIO理論基礎知識中，分別對基本結構和工作模式進行了詳細的介紹。GPIO基本結構中主要對GPIO內部的各個功能電路逐一的進行的分析；GPIO工作模式中主要介紹GPIO應用在不同的使用場景下，GPIO埠的靜態特征配置和動態的工作模式，同時對信號的工作流進行了分析。 ...
  • CentOS7-啟動|重啟|停止|狀態服務腳本
    源碼編譯安裝方法 1、上傳包nginx-1.10.0.tar.gz至服務端 # 解壓到工作目錄 [root@template ~]# tar xf nginx-1.10.0.tar.gz -C /usr/local/src/ # 切換至Nginx目錄下，找到configure [root@templ ...
  • rk3399 gpio 設置
    rk3399 GPIO的操作方法 RK3399 GPIO分為4個大組 0 ~ 4，每個大組分為4個小組（A,B,C,D)，比如GPIO0 有 A,B,C,D四個小組，每個小組有A0~A7個gpio 一個GPIO需要三類功能支持：gpio時鐘，gpio輸入輸出，這個pin要設置為gpio功能（復用） ...
  • 物聯網技術基礎及應用緒論
    物聯網技術基礎及應用緒論 任課教師：北京化工大學 畢超 課程鏈接：https://www.bilibili.com/video/BV1ha411g7H2/?spm_id_from=333.999.0.0&vd_source=e66dd25b0246f28e772d75f11c80f03c 課程版本： ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類