神奇的 SQL 之子查詢，細(xì)節(jié)滿滿 ！

前言

　　開心一刻

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

　　　　麻雀問：你是啥子鳥喲 ？

　　　　烏鴉說：我是鳳凰。

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

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



子查詢

　　講子查詢之前，我們先來看看視圖，何謂視圖 ？?視圖是基于 SQL
語句的結(jié)果集的可視化的表，包含行和列，就像一個(gè)真實(shí)的表，但只是一張?zhí)摂M表，我們可以將其視作為一張普通的表；視圖只供數(shù)據(jù)查詢，不能進(jìn)行數(shù)據(jù)更改，也不能保存數(shù)據(jù)，查詢數(shù)據(jù)來源于我們的實(shí)體表；說的簡單點(diǎn)，視圖就是復(fù)雜
SELECT 語句的一個(gè)代號(hào)，為查詢提供便利。視圖總是顯示最近的數(shù)據(jù)，每當(dāng)我們查詢視圖時(shí)，數(shù)據(jù)庫引擎通過使用 SQL 語句來重建數(shù)據(jù)。

　　那何謂子查詢，它與視圖又有何關(guān)系 ？ 視圖是持久化的 SELECT 語句，而子查詢就是將定義視圖的 SELECT 語句直接用于 FROM
子句當(dāng)中，它是個(gè)一次性的視圖，在 SELECT
語句執(zhí)行完之后就會(huì)消失。光說概念，可能還是不太好理解，我們來看下視圖與子查詢的具體示例，通過示例我們就能更好的理解了

　　假設(shè)我們有如下表
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:凍結(jié)資金,3:優(yōu)惠', amount DECIMAL(22,6) NOT
NULL DEFAULT '0.00000' COMMENT '額度值', create_by VARCHAR(50) NOT NULL COMMENT '
創(chuàng)建者', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
update_timeDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間', 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 個(gè)視圖
-- 自由資金 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; -- 凍結(jié)資金 DROP VIEW IF EXISTS view_freeze; CREATE VIEW
view_freeze(login_name, freezeAmount)AS SELECT login_name, amount FROM
t_customer_creditWHERE credit_type = 2; -- 優(yōu)惠 DROP VIEW IF EXISTS
view_promotion;CREATE VIEW view_promotion(login_name, promotionAmount) AS SELECT
login_name, amountFROM t_customer_credit WHERE credit_type = 3; View Code
　　那么我們可以用如下 SQL 來顯示用戶的三個(gè)額度
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.promotionAmountFROM ( SELECT login_name, amount freeAmount FROM
t_customer_creditWHERE credit_type = 1 ) free LEFT JOIN ( SELECT login_name,
amount freezeAmountFROM t_customer_credit WHERE credit_type = 2 ) freeze ON
free.login_name= freeze.login_name LEFT JOIN ( SELECT login_name, amount
promotionAmountFROM t_customer_credit WHERE credit_type = 3 ) promotion ON
free.login_name= promotion.login_name; View Code
　　注意 SQL 的執(zhí)行順序，子查詢作為內(nèi)層查詢會(huì)首先執(zhí)行；原則上子查詢必須設(shè)定名稱，所以我們盡量從處理內(nèi)容的角度出發(fā)為子查詢設(shè)定一個(gè)恰當(dāng)?shù)拿Q

普通子查詢


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

　　假設(shè)我們有商品表：t_commodity
DROP TABLE IF EXISTS t_commodity; CREATE TABLE t_commodity ( id INT(11)
unsignedNOT NULL AUTO_INCREMENT COMMENT '自增主鍵', serial_number VARCHAR(32) NOT
NULL COMMENT '編號(hào)', name VARCHAR(50) NOT NULL COMMENT '名稱', category VARCHAR(50)
NOT NULL COMMENT '類別', sell_unit_price DECIMAL(22,6) NOT NULL COMMENT '出售單價(jià)',
purchase_unit_priceDECIMAL(22,6) NOT NULL COMMENT '進(jìn)貨單價(jià)', create_time DATETIME
NOT NULL COMMENT '創(chuàng)建時(shí)間', update_time DATETIME NOT NULL COMMENT '更新時(shí)間', primary
key(id) ) COMMENT '商品表'; -- 初始數(shù)據(jù) 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'
,'運(yùn)動(dòng)短褲', '褲子', '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
　　現(xiàn)在我們要實(shí)現(xiàn)如下要求：統(tǒng)計(jì)出各個(gè)類別下商品的數(shù)量，我們可以寫出如下 SQL
-- 最容易想到的 GROUP BY SELECT category, COUNT(*) cnt FROM t_commodity GROUP BY
category-- 子查詢實(shí)現(xiàn)，這里貌似多此一舉，權(quán)且當(dāng)做一個(gè)示例 -- 普通子查詢一般應(yīng)用于多表之間的查詢，比如 學(xué)生表、課程表、選課表 之間的一些查詢
SELECT * FROM ( SELECT category, COUNT(*) cnt FROM t_commodity GROUP BY
category ) cs; View Code
標(biāo)量子查詢

　　普通子查詢一般是返回多行結(jié)果（偶爾也會(huì)只返回 1 行，有時(shí)也會(huì)查不到結(jié)果）；當(dāng)返回結(jié)果是 1 行 1
列時(shí)，該子查詢被稱作標(biāo)量子查詢，標(biāo)量子查詢有個(gè)特殊的限制，必須而且只能返回 1 行 1 列的結(jié)果。

　　說的簡單點(diǎn)：標(biāo)量子查詢就是返回單一值的子查詢。由于返回值是單一值，所以標(biāo)量子查詢可以用在 = 或 <>
這樣需要單一值的比較運(yùn)算符之中，這也正是其優(yōu)勢所在。我們來看一些簡單的例子，還是以?t_commodity 為例，假設(shè)我們有如下需求，我們該如何實(shí)現(xiàn)它

　　　　1、查詢出售單價(jià)高于平均出售單價(jià)的商品

　　　　2、查詢所有商品信息，并在每個(gè)商品的信息中加入平均出售單價(jià)、平均進(jìn)貨單價(jià)

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

　　查詢 1

　　　　第一感覺，我們也許會(huì)寫出如下的 SQL
-- 錯(cuò)誤的 SQL SELECT * FROM t_commodity WHERE sell_unit_price > AVG
(sell_unit_price);
　　　　實(shí)際上這個(gè) SQL 執(zhí)行會(huì)報(bào)錯(cuò)，WHERE 子句中不能使用聚合函數(shù)。那這個(gè)查詢要怎么寫了，此時(shí)標(biāo)量子查詢就派上用場了，SQL 如下
-- 查詢出售單價(jià)高于平均出售單價(jià)的商品 SELECT * FROM t_commodity WHERE sell_unit_price > ( SELECT
AVG(sell_unit_price) FROM t_commodity );
　　查詢 2

　　　　這個(gè) SQL 應(yīng)該比較容易想到，SELECT 子句中加入?平均出售單價(jià)、平均進(jìn)貨單價(jià) 列即可，如下
-- 查詢所有商品信息，并在每個(gè)商品的信息中加入平均出售單價(jià)、平均進(jìn)貨單價(jià) SELECT *, (SELECT AVG(sell_unit_price)
FROM t_commodity) avg_sell_price, (SELECT AVG(purchase_unit_price) FROM
t_commodity) avg_purchase_priceFROM t_commodity;
　　查詢 3

　　　　先以類別進(jìn)行分組，然后取分組后各個(gè)類別的平均出售價(jià)格，與全部商品的平均出售價(jià)格比較，過濾出滿足條件的類別，SQL 如下
-- 按照商品類別分類，查詢出平均出售單價(jià)高于全部商品的平均出售單價(jià)的商品類別（類別名、類別平均出售單價(jià)） SELECT category, AVG
(sell_unit_price) category_avg_sell_priceFROM t_commodity GROUP BY category
HAVING AVG(sell_unit_price) > ( SELECT AVG(sell_unit_price) FROM t_commodity )


　　使用標(biāo)量子查詢時(shí)，我們需要注意一點(diǎn)：我們要明確的知道該子查詢返回的結(jié)果就是單一值，絕對不能返回多行結(jié)果。不然執(zhí)行會(huì)報(bào)錯(cuò)

關(guān)聯(lián)子查詢


　　關(guān)聯(lián)子查詢是指一個(gè)包含對表的引用的子查詢，該表也顯示在外部查詢中。通俗一點(diǎn)來講，就是子查詢引用到了主查詢的數(shù)據(jù)數(shù)據(jù)。在關(guān)聯(lián)子查詢中，對于外部查詢返回的每一行數(shù)據(jù)，內(nèi)部查詢都要執(zhí)行一次。另外，在關(guān)聯(lián)子查詢中是信息流是雙向的，外部查詢的每行數(shù)據(jù)傳遞一個(gè)值給子查詢，然后子查詢?yōu)槊恳恍袛?shù)據(jù)執(zhí)行一次并返回它的記錄。然后，外部查詢根據(jù)返回的記錄做出決策。光看概念，晦澀難懂，我們結(jié)合具體的例子來看關(guān)聯(lián)子查詢

　　還是以商品表：t_commodity? 為例，如何選取出各商品類別中高于該類別平均出售價(jià)格的商品，可能大家還沒明白這個(gè)需求，那么我們具體點(diǎn)

　　　　所有商品的類別、出售價(jià)格如下



　　　　各類別及類別平均出售價(jià)格如下



?　　　　我們得到的正確結(jié)果應(yīng)該是



　　這個(gè) SQL 我們要如何寫？ 像這樣
-- 錯(cuò)誤的 SQL SELECT * FROM t_commodity WHERE sell_unit_price > ( SELECT AVG
(sell_unit_price)FROM t_commodity GROUP BY category )
　　是肯定不行的，那正確的打開方式應(yīng)該是怎么樣的了，此時(shí)需要關(guān)聯(lián)子查詢上場了，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)
至關(guān)重要，它的作用是在同一商品類別中對各商品的出售單價(jià)與平均單價(jià)進(jìn)行比較。在對表中某一部分記錄的集合進(jìn)行比較時(shí)，就可以使用關(guān)聯(lián)子查詢，當(dāng)出現(xiàn) “限定” 或
“限制” 這樣的詞匯時(shí)，通常會(huì)使用關(guān)聯(lián)子查詢。

　　在關(guān)聯(lián)子查詢中，對于外部查詢返回的每一行數(shù)據(jù)，內(nèi)部查詢都要執(zhí)行一次，DBMS 內(nèi)部的執(zhí)行結(jié)果類似如下



總結(jié)

　　1、SQL 執(zhí)行順序
(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
等運(yùn)算符一起使用，使用起來也是非常靈活的；標(biāo)量子查詢出現(xiàn)的位置就更靈活了，并不僅僅局限于 WHERE
子句中，通常任何可以使用單一值的位置都可以使用，SELECE 子句、GROUP BY 子句、HAVING 子句、ORDER BY
子句，也就是說，能夠使用常量或者列名的地方，都可以使用標(biāo)量子查詢。

　　3、效率問題


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

參考

　　《SQL基礎(chǔ)教程》

　　《SQL進(jìn)階教程》

• ? 上一篇：Windbg程序調(diào)試系列5-高CPU問題分析
• ? 下一篇：搭建nuget 服務(wù)器