神奇的 SQL 之子查詢，細節滿滿 ！

来源:https://www.cnblogs.com/youzhibing/archive/2019/08/08/11304864.html
-Advertisement-
Play Games

前言 開心一刻 有一天，麻雀遇見一隻烏鴉。 麻雀問：你是啥子鳥喲 ？ 烏鴉說：我是鳳凰。 麻雀說：哪有你龜兒子這麼黢黑的鳳凰 ？ 烏鴉說：你懂個鏟鏟，老子是燒鍋爐的鳳凰。 子查詢 講子查詢之前，我們先來看看視圖，何謂視圖 ？ 視圖是基於 SQL 語句的結果集的可視化的表，包含行和列，就像一個真實的表 ...


前言

　　開心一刻

　　　　有一天，麻雀遇見一隻烏鴉。

　　　　麻雀問：你是啥子鳥喲 ？

　　　　烏鴉說：我是鳳凰。

　　　　麻雀說：哪有你龜兒子這麼黢黑的鳳凰 ？

　　　　烏鴉說：你懂個鏟鏟，老子是燒鍋爐的鳳凰。

子查詢

　　講子查詢之前，我們先來看看視圖，何謂視圖 ？ 視圖是基於 SQL 語句的結果集的可視化的表，包含行和列，就像一個真實的表，但只是一張虛擬表，我們可以將其視作為一張普通的表；視圖只供數據查詢，不能進行數據更改，也不能保存數據，查詢數據來源於我們的實體表；說的簡單點，視圖就是複雜 SELECT 語句的一個代號，為查詢提供便利。視圖總是顯示最近的數據，每當我們查詢視圖時，資料庫引擎通過使用 SQL 語句來重建數據。

　　那何謂子查詢，它與視圖又有何關係 ？ 視圖是持久化的 SELECT 語句，而子查詢就是將定義視圖的 SELECT 語句直接用於 FROM 子句當中，它是個一次性的視圖，在 SELECT 語句執行完之後就會消失。光說概念，可能還是不太好理解，我們來看下視圖與子查詢的具體示例，通過示例我們就能更好的理解了

　　假設我們有如下表

CREATE TABLE t_customer_credit (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    login_name VARCHAR(50) NOT NULL COMMENT '登錄名',
    credit_type TINYINT(1) NOT NULL COMMENT '額度類型，1:自由資金,2:凍結資金,3:優惠',
    amount DECIMAL(22,6) NOT NULL DEFAULT '0.00000' COMMENT '額度值',
    create_by VARCHAR(50) NOT NULL COMMENT '創建者',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '創建時間',
    update_by VARCHAR(50) NOT NULL COMMENT '修改者',
  PRIMARY KEY (id)
);
INSERT INTO `t_customer_credit` VALUES (1, 'zhangsan', 1, 550.000000, 'system', '2019-7-7 11:30:09', '2019-7-8 20:21:05', 'system');
INSERT INTO `t_customer_credit` VALUES (2, 'zhangsan', 2, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (3, 'zhangsan', 3, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (4, 'lisi', 1, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (5, 'lisi', 2, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (6, 'lisi', 3, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
View Code

　　以及如下 3 個視圖

-- 自由資金
DROP VIEW IF EXISTS view_free;
CREATE VIEW view_free(login_name, freeAmount) AS 
SELECT login_name, amount
FROM t_customer_credit
WHERE credit_type = 1;

-- 凍結資金
DROP VIEW IF EXISTS view_freeze;
CREATE VIEW view_freeze(login_name, freezeAmount) AS 
SELECT login_name, amount
FROM t_customer_credit
WHERE credit_type = 2;

-- 優惠
DROP VIEW IF EXISTS view_promotion;
CREATE VIEW view_promotion(login_name, promotionAmount) AS 
SELECT login_name, amount
FROM t_customer_credit
WHERE credit_type = 3;
View Code

　　那麼我們可以用如下 SQL 來顯示用戶的三個額度

SELECT v1.login_name, v1.free_amount, v2.freeze_amount, v3.promotion_amount FROM
view_free v1 
LEFT JOIN view_freeze v2 ON v1.login_name = v2.login_name
LEFT JOIN view_promotion v3 ON v1.login_name = v3.login_name;

　　換成子查詢的方式，SQL 如下

SELECT free.login_name, free.freeAmount, freeze.freezeAmount, promotion.promotionAmount
FROM 
(
    SELECT login_name, amount freeAmount
    FROM t_customer_credit
    WHERE credit_type = 1
) free 
LEFT JOIN
(
    SELECT login_name, amount freezeAmount
    FROM t_customer_credit
    WHERE credit_type = 2
) freeze ON free.login_name = freeze.login_name
LEFT JOIN
(
    SELECT login_name, amount promotionAmount
    FROM t_customer_credit
    WHERE credit_type = 3
) promotion ON free.login_name = promotion.login_name;
View Code

　　註意 SQL 的執行順序，子查詢作為內層查詢會首先執行；原則上子查詢必須設定名稱，所以我們儘量從處理內容的角度出發為子查詢設定一個恰當的名稱

普通子查詢

　　上面講到的子查詢就是普通子查詢，非要給個定義的話，就是返回多行結果的子查詢。這個在實際應用中還是用的非常多的，這個相信大家都比較熟悉，不做過多的說明，只舉個簡單例子

　　假設我們有商品表：t_commodity

DROP TABLE IF EXISTS t_commodity;
CREATE TABLE t_commodity (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    serial_number VARCHAR(32) NOT NULL COMMENT '編號',
    name VARCHAR(50) NOT NULL COMMENT '名稱',
    category VARCHAR(50) NOT NULL COMMENT '類別',
    sell_unit_price DECIMAL(22,6) NOT NULL COMMENT '出售單價',
    purchase_unit_price DECIMAL(22,6) NOT NULL COMMENT '進貨單價',
    create_time DATETIME NOT NULL COMMENT '創建時間',
    update_time DATETIME NOT NULL COMMENT '更新時間',
    primary key(id)
) COMMENT '商品表';

-- 初始數據
INSERT INTO t_commodity(serial_number, name, category, sell_unit_price, purchase_unit_price, create_time, update_time)
VALUES
('0001', 'T恤衫', '衣服', '80', '20', NOW(), NOW()),
('0002', '羽絨服', '衣服', '280.5', '100', NOW(), NOW()),
('0003', '休閑褲', '褲子', '50', '15.5', NOW(), NOW()),
('0004', '運動短褲', '褲子', '30', '10', NOW(), NOW()),
('0005', '菜刀', '廚具', '35', '10', NOW(), NOW()),
('0006', '鍋鏟', '廚具', '15', '6.5', NOW(), NOW()),
('0007', '鍋', '廚具', '60', '20', NOW(), NOW()),
('0008', '電飯煲', '廚具', '240', '70', NOW(), NOW()),
('0009', '打孔器', '辦公用品', '50', '10.5', NOW(), NOW()),
('0010', '文件架', '辦公用品', '35', '13', NOW(), NOW()),
('0011', '辦公桌', '辦公用品', '280', '120', NOW(), NOW()),
('0012', '辦公椅', '辦公用品', '256', '100', NOW(), NOW());
View Code

　　現在我們要實現如下要求：統計出各個類別下商品的數量，我們可以寫出如下 SQL

-- 最容易想到的 GROUP BY
SELECT category, COUNT(*) cnt FROM t_commodity
GROUP BY category

-- 子查詢實現，這裡貌似多此一舉，權且當做一個示例
-- 普通子查詢一般應用於多表之間的查詢，比如 學生表、課程表、選課表 之間的一些查詢
SELECT * FROM
(
    SELECT category, COUNT(*) cnt FROM t_commodity
    GROUP BY category
) cs;
View Code

標量子查詢

　　普通子查詢一般是返回多行結果（偶爾也會只返回 1 行，有時也會查不到結果）；當返回結果是 1 行 1 列時，該子查詢被稱作標量子查詢，標量子查詢有個特殊的限制，必須而且只能返回 1 行 1 列的結果。

　　說的簡單點：標量子查詢就是返回單一值的子查詢。由於返回值是單一值，所以標量子查詢可以用在 = 或 <> 這樣需要單一值的比較運算符之中，這也正是其優勢所在。我們來看一些簡單的例子，還是以 t_commodity 為例，假設我們有如下需求，我們該如何實現它

　　　　1、查詢出售單價高於平均出售單價的商品

　　　　2、查詢所有商品信息，併在每個商品的信息中加入平均出售單價、平均進貨單價

　　　　3、按照商品類別分類，查詢出平均出售單價高於全部商品的平均出售單價的商品類別（類別名、類別平均出售單價）

　　查詢 1

　　　　第一感覺，我們也許會寫出如下的 SQL

-- 錯誤的 SQL
SELECT * FROM t_commodity
WHERE sell_unit_price > AVG(sell_unit_price);

　　　　實際上這個 SQL 執行會報錯，WHERE 子句中不能使用聚合函數。那這個查詢要怎麼寫了，此時標量子查詢就派上用場了，SQL 如下

-- 查詢出售單價高於平均出售單價的商品
SELECT * FROM t_commodity
WHERE sell_unit_price > (
    SELECT AVG(sell_unit_price) 
    FROM t_commodity
);

　　查詢 2

　　　　這個 SQL 應該比較容易想到，SELECT 子句中加入 平均出售單價、平均進貨單價 列即可，如下

-- 查詢所有商品信息，併在每個商品的信息中加入平均出售單價、平均進貨單價
SELECT *,
    (SELECT AVG(sell_unit_price) FROM t_commodity) avg_sell_price,
    (SELECT AVG(purchase_unit_price) FROM t_commodity) avg_purchase_price
FROM t_commodity;

　　查詢 3

　　　　先以類別進行分組，然後取分組後各個類別的平均出售價格，與全部商品的平均出售價格比較，過濾出滿足條件的類別，SQL 如下

-- 按照商品類別分類，查詢出平均出售單價高於全部商品的平均出售單價的商品類別（類別名、類別平均出售單價）
SELECT category, AVG(sell_unit_price) category_avg_sell_price
FROM t_commodity
GROUP BY category
HAVING AVG(sell_unit_price) > (
    SELECT AVG(sell_unit_price) 
    FROM t_commodity
)

　　使用標量子查詢時，我們需要註意一點：我們要明確的知道該子查詢返回的結果就是單一值，絕對不能返回多行結果。不然執行會報錯

關聯子查詢

　　關聯子查詢是指一個包含對錶的引用的子查詢，該表也顯示在外部查詢中。通俗一點來講，就是子查詢引用到了主查詢的數據數據。在關聯子查詢中，對於外部查詢返回的每一行數據，內部查詢都要執行一次。另外，在關聯子查詢中是信息流是雙向的，外部查詢的每行數據傳遞一個值給子查詢，然後子查詢為每一行數據執行一次並返回它的記錄。然後，外部查詢根據返回的記錄做出決策。光看概念，晦澀難懂，我們結合具體的例子來看關聯子查詢

　　還是以商品表：t_commodity  為例，如何選取出各商品類別中高於該類別平均出售價格的商品，可能大家還沒明白這個需求，那麼我們具體點

　　　　所有商品的類別、出售價格如下

　　　　各類別及類別平均出售價格如下

 　　　　我們得到的正確結果應該是

　　這個 SQL 我們要如何寫？ 像這樣

-- 錯誤的 SQL
SELECT * FROM t_commodity
WHERE sell_unit_price > (
    SELECT AVG(sell_unit_price) FROM t_commodity
    GROUP BY category
)

　　是肯定不行的，那正確的打開方式應該是怎麼樣的了，此時需要關聯子查詢上場了，SQL如下

SELECT * FROM t_commodity t1
WHERE sell_unit_price > (
    SELECT AVG(sell_unit_price) FROM t_commodity t2
    WHERE t1.category = t2.category
    GROUP BY category
)

 　　子查詢中的 WHERE 子句(WHERE t1.category = t2.category) 至關重要，它的作用是在同一商品類別中對各商品的出售單價與平均單價進行比較。在對錶中某一部分記錄的集合進行比較時，就可以使用關聯子查詢，當出現 “限定” 或 “限制” 這樣的辭彙時，通常會使用關聯子查詢。

　　在關聯子查詢中，對於外部查詢返回的每一行數據，內部查詢都要執行一次，DBMS 內部的執行結果類似如下

總結

　　1、SQL 執行順序

(8) SELECT  (9) DISTINCT (11) <TOP_specification> <select_list>
(1)  FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

　　2、書寫位置

　　　　子查詢可以在 SELECT、INSERT、UPDATE 和 DELETE 語句中，同 =、<、>、>=、<=、IN、BETWEEN 等運算符一起使用，使用起來也是非常靈活的；標量子查詢出現的位置就更靈活了，並不僅僅局限於 WHERE 子句中，通常任何可以使用單一值的位置都可以使用，SELECE 子句、GROUP BY 子句、HAVING 子句、ORDER BY 子句，也就是說，能夠使用常量或者列名的地方，都可以使用標量子查詢。

　　3、效率問題

　　　　子查詢的效率一直都是一個比較頭疼的問題，加合適的索引能改善效率，但也只是局限於很少的情況；如果數據量大，對性能要求又高，能不用子查詢就儘量別用子查詢，儘量用其他的方式替代，很多情況下，子查詢可以用關聯查詢來替代

參考

　　《SQL基礎教程》

　　《SQL進階教程》


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

-Advertisement-
Play Games
更多相關文章
  • MySQL入門——MySQL資料庫和SQL語言
    本文主要瞭解了MySQL關係型資料庫和SQL語言的基本知識。 ...
  • mysql修改max_allowed_packet數據包最大值
    在windows環境下！！！！ 1.找到my.inc文件，不是你的安裝目錄路徑，是C:\ProgramData\MySQL\MySQL Server 5.7這個路徑，註意 ProgramData 文件夾是隱藏的需要顯示出來！！！ C:\ProgramData\MySQL\MySQL Server 5 ...
  • MySQL存儲過程02
    這次接著說MySQL存儲過程： 我們先看它的多分支控制結構case: case的語句很簡單： case 變數名 when 條件1 then 輸出結果1; when 條件2 then 輸出結果2; ...... end case; 那我們就來建立一個存儲過程實現它： 由於我們設置的是隨機數，所以它會根 ...
  • 將 雲資料庫MongoDB(阿裡雲)物理備份文件下載恢復至本地自建資料庫 遇到的5個問題
    有時候我們可能需要將雲上資料庫下載到本地，下麵是我們在操作MongoDB資料庫時遇到的五個小問題。 其實現在RDS的 幫助文檔 寫的都比較詳細了，大家在第一次操作時，可以細讀一下，避免一些不必要的問題。 Problem 1 通過wget命令下載 雲MongoDB物理備份文件 時，url 中間有特殊字 ...
  • 如何安裝Oracle--新手安裝Oracle教程
    1、 將win32_11gR2_database_1of2.zip與win32_11gR2_database_1of2.zip 解壓到當前目錄 PS:選中兩個壓縮包後右鍵解壓到當前文件夾；必須同時解壓，不能單獨解壓否則會覆蓋文件） 2、 解壓完成後設置文件相容性： 進入 database文件夾 點擊 ...
  • SQL Server直接執行.sql文件
    SQL Server直接執行.sql文件 客戶的資料庫數據被篡改，利用Log Explorer工具根據日誌生成的回滾腳本有200多M，不可能一下子扔到查詢分析器里去執行，於是想是否SQL Server是否可以像Oracle那樣直接執行.sql文件。講過查資料，測試，發現可以在cmd視窗中執行如下命令 ...
  • 多個結果集union後保持各自原有排序
    SELECT *FROM ( SELECT TOP (@count1) a.* FROM Article AS a WITH (NOLOCK)LEFT JOIN Article_Type AS at WITH (NOLOCK)ON a.ArticleType=at.ArticleTypeIdWHER ...
  • PLSQL 12 安裝、連接Oracle
    點擊下載PLSQL，本次安裝的PLSQL版本為12.0.7，建議安裝64位。 點擊下載PLSQL，本次安裝的PLSQL版本為12.0.7，建議安裝64位。 下載PLSQL時，版本旁邊會有個“Language pack”的鏈接，點擊後左側選擇“Chinese”即可下載漢化包。 註冊碼 下載PLSQL時 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類