來源：數據STUDIO

作者：云朵君

查詢"01"課程比"02"課程成績高的學生信息及課程分數

分析

第一步：根據需要查詢的最終結果確認所需用到的表："學生信息及課程分數"，需要用到學生信息表與成績表；

第二步：確認條件："01"課程比"02"課程成績高的學生，需要先分別查出"01"課程的成績與"02"課程，再根據條件"成績更高"篩選出學生；

 #查詢01課程的成績  SELECT * FROM sc WHERE c_id='01';
  
  #查詢02課程的成績  SELECT * FROM sc WHERE c_id='02';

第三步：查詢：根據第二步篩選出的學生，查詢出對應學生的"學生信息及課程分數"。

語句

SELECT stu.*,sc.c_id,sc.score FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id WHERE t1.score>t2.score;

結果

s_ids_names_births_sexc_idscore02錢電1990-12-21男017002錢電1990-12-21男026002錢電1990-12-21男038004李云1990-08-06男015004李云1990-08-06男023004李云1990-08-06男0320

如果要求課程分數需要"課程名稱"與"對應分數"，則還需要跟課程表連接以顯示課程名及分數。

語句

SELECT stu.*,co.c_name,sc.score FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id JOIN co ON sc.c_id=co.c_id WHERE t1.score>t2.score;

結果

s_ids_names_births_sexc_namescore02錢電1990-12-21男語文7004李云1990-08-06男語文5002錢電1990-12-21男數學6004李云1990-08-06男數學3002錢電1990-12-21男英語8004李云1990-08-06男英語20

一、利用子查詢進行連接表

以上例子中先分別查出"01"課程的成績與"02"課程，再根據查詢出的結果去查詢對應學生信息及課程成績，即用到本節將要介紹的子查詢。

在SELECT語句中，子查詢總是從內向外處理。在處理上面的SELECT語句時，MySQL實際上執行了兩個操作。

首先，它執行下面的查詢：

SELECT t1.* FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id WHERE t1.score>t2.score;

此時得到兩個學生編號s_id等于02和04表格：

s_idc_idscore020170040150

然后，將得到的表格t與另外兩個表格連接后再查詢。

SELECT stu.*,sc.c_id,sc.score FROM t JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id

其實這里也分了兩步：

SELECT stu.*,sc.c_id,sc.score FROM t JOIN stu ON t1.s_id=stu.s_id

結果：

s_ids_names_births_sexc_idscore02錢電1990-12-21男017004李云1990-08-06男0150

這里只得到了這兩個學生的課程1的成績，結果需要查詢到這兩個學生所有課程的成績，因此需要將上述得到的表格tt再與成績表連接。

SELECT stu.*,sc.c_id,sc.score FROM tt JOIN sc ON stu.s_id=sc.s_id

格式化SQL 包含子查詢的SELECT 語句難以閱讀和調試，特別是它們較為復雜時更是如此。如上所示把子查詢分解為多行并且適當地進行縮進，能極大地簡化子查詢的使用。

二、利用子查詢進行過濾

查詢沒學過"張三"老師授課的同學的信息

首先需要查詢出"張三"老師授課信息：

SELECT stu.s_id FROM te LEFT JOIN co ON te.t_id=co.t_id LEFT JOIN sc ON co.c_id=sc.c_id LEFT JOIN stu ON sc.s_id=stu.s_id WHERE t_name='張三';

得到"張三"老師授課信息s_id為01，02，03，04，05，07。然后，這兩個值以 NOT IN 操作符要求的逗號分隔的格式傳遞給外部查詢的 WHERE 子句。

外部查詢變成：

SELECT * FROM stu WHERE s_id NOT IN (01,02,03,04,05,07);

結果：

s_ids_names_births_sex06吳蘭1992-03-01女08王菊1990-01-20女

可見，在 WHERE子句中使用子查詢能夠編寫出功能很強并且很靈活的SQL語句。對于能嵌套的子查詢的數目沒有限制，不過在實際使用時由于性能的限制，不能嵌套太多的子查詢。

列必須匹配 在WHERE子句中使用子查詢（如這里所示），應該保證SELECT語句具有與WHERE 子句中相同數目的列。通常，子查詢將返回單個列并且與單個列匹配，但如果需要也可以使用多個列。

三、作為計算字段使用子查詢

使用子查詢的另一方法是創建計算字段。

查詢每位學生選修的課程數

首先可使用 SELECT COUNT(*)對表中的行進行計數，并且通過提供一條WHERE子句來過濾某個特定的學生，可僅對該學生的課程進行計數。

SELECT COUNT(c_id) FROM sc WHERE s_id = '01';

為了對每個學生執行COUNT(*)計算，應該將COUNT(*)作為一個子查詢。

SELECT *, (SELECT COUNT(c_id)
		   FROM sc
           WHERE sc.s_id = stu.s_id) AS cos FROM stu;

結果：

s_ids_names_births_sexcos01趙雷1990-01-01男302錢電1990-12-21男303孫風1990-05-20男304李云1990-08-06男305周梅1991-12-01女206吳蘭1992-03-01女207鄭竹1992-04-21女208王菊1990-01-20女0

這條SELECT語句對customers表中每個學生返回5列：s_id，s_name，s_birth，s_sex和cos。cos是一個計算字段，它是由圓括號中的子查詢建立的。該子查詢對檢索出的每個學生執行一次。在此例子中，該子查詢執行了8次，因為檢索出了8個學生。

子查詢中的 WHERE子句使用了完全限定列名，任何時候只要列名可能有多義性，就必須使用這種語法（表名和列名由一個句點分隔）。如果不使用完全限定的列名會與本身匹配。

逐漸增加子查詢來建立查詢 用子查詢測試和調試查詢很有技巧性，特別是在這些語句的復雜性不斷增加的情況下更是如此。用子查詢建立（和測試）查詢的最可靠的方法是逐漸進行，這與MySQL處理它們的方法非常相同。首先，建立和測試最內層的查詢。然后，用硬編碼數據建立和測試外層查詢，并且僅在確認它正常后才嵌入子查詢。這時，再次測試它。對于要增加的每個查詢，重復這些步驟。這樣做僅給構造查詢增加了一點點時間，但節省了以后（找出查詢為什么不正常）的大量時間，并且極大地提高了查詢一開始就正常工作的可能性。

四、附錄

數據庫建立即數據導入準備

-- 創建數據庫school CREATE DATABASE school; -- 選擇進入school數據庫 USE school; -- ------------建表導數------------- -- 創建stu CREATE TABLE stu(
s_id VARCHAR(10) PRIMARY KEY,
s_name VARCHAR(10) NOT NULL,
s_birth DATE,
s_sex VARCHAR(10)) -- 導入數據 INSERT INTO stu VALUES ('01' , '趙雷' , '1990-01-01' , '男'),
('02' , '錢電' , '1990-12-21' , '男'),
('03' , '孫風' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吳蘭' , '1992-03-01' , '女'),
('07' , '鄭竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女'); SELECT * FROM stu; -- 檢查數據 SELECT COUNT(*) FROM stu; -- 檢查總行數 -- 創建co CREATE TABLE co(
c_id VARCHAR(10) PRIMARY KEY,
c_name VARCHAR(10),
t_id VARCHAR(10)); -- 導入數據 INSERT INTO co VALUES ('01' , '語文' , '02'),
('02' , '數學' , '01'),
('03' , '英語' , '03'); SELECT * FROM co; -- 檢查數據 SELECT COUNT(*) FROM co; -- 檢查總行數 -- 創建te CREATE TABLE te(
t_id VARCHAR(10) PRIMARY KEY,
t_name VARCHAR(10)); -- 導入數據 INSERT INTO te VALUES ('01' , '張三'),
('02' , '李四'),
('03' , '王五'); SELECT * FROM te; -- 檢查數據 SELECT COUNT(*) FROM te; -- 檢查總行數 -- 創建sc CREATE TABLE sc(
s_id VARCHAR(10),
c_id VARCHAR(10),
score int); -- 導入數據 INSERT INTO sc VALUES ('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98); SELECT * FROM sc; -- 檢查數據 SELECT COUNT(*) FROM sc; -- 檢查總行數