在 MySQL 日常開發(fā)里，單表查詢只能處理最簡單的數(shù)據(jù)需求，真正的業(yè)務(wù)場景幾乎都要用到復(fù)合查詢—— 也就是多表關(guān)聯(lián)、嵌套查詢、自連接、結(jié)果合并這類高級(jí)查詢。

今天這篇文章，我就帶著大家把復(fù)合查詢從基礎(chǔ)到實(shí)戰(zhàn)徹底講透，每一個(gè)知識(shí)點(diǎn)都配案例 + 解釋，小白也能輕松學(xué)會(huì)。

一、先回顧：單表基礎(chǔ)查詢（溫故知新）

復(fù)合查詢是單表查詢的進(jìn)階，我們先用幾個(gè)經(jīng)典案例快速過一遍重點(diǎn)語法。

1. 多條件篩選

查詢工資高于 500 或崗位是 MANAGER，且姓名以 J 開頭的員工：

SELECT * FROM EMP 
WHERE (sal>500 OR job='MANAGER') 
AND ename LIKE 'J%';

2. 多字段排序

按部門號(hào)升序、同部門內(nèi)工資降序：

SELECT * FROM EMP ORDER BY deptno asc, sal DESC;

3. 計(jì)算年薪并排序

獎(jiǎng)金為空時(shí)用 IFNULL 轉(zhuǎn) 0，避免計(jì)算錯(cuò)誤：

SELECT ename, sal*12+IFNULL(comm,0) AS '年薪' 
FROM EMP 
ORDER BY 年薪 DESC;

4. 聚合函數(shù)搭配子查詢

查工資最高的員工：

SELECT ename, job FROM EMP 
WHERE sal = (SELECT MAX(sal) FROM EMP);

查高于平均工資的員工：

SELECT ename, sal FROM EMP 
WHERE sal > (SELECT AVG(sal) FROM EMP);

5. 分組統(tǒng)計(jì) + 分組后過濾

每個(gè)部門平均工資（保留兩位小數(shù)）、最高工資：

SELECT deptno, FORMAT(AVG(sal), 2), MAX(sal) 
FROM EMP 
GROUP BY deptno;

這里的FORMAT 是 MySQL 里專門用來「格式化數(shù)字 / 日期」的函數(shù)，最常用作用是：把數(shù)字保留指定位小數(shù)、加千分位分隔符。標(biāo)準(zhǔn)格式：FORMAT(數(shù)字, 保留小數(shù)位數(shù))。

平均工資低于 2000 的部門：

SELECT deptno, AVG(sal) AS avg_sal 
FROM EMP 
GROUP BY deptno 
HAVING avg_sal < 2000;

二、多表查詢：跨表取數(shù)的核心

實(shí)際開發(fā)中，數(shù)據(jù)分散在多張表里，必須用多表連接才能拿到完整信息。

本文用經(jīng)典 3 張表演示：

• EMP：員工表（員工號(hào)、姓名、崗位、工資、部門號(hào)…）
• DEPT：部門表（部門號(hào)、部門名、位置…）
• SALGRADE：工資等級(jí)表（等級(jí)、最低工資、最高工資）

1. 什么是笛卡爾積

不加連接條件直接查多張表，會(huì)出現(xiàn)全組合，數(shù)據(jù)量爆炸，絕對(duì)不能用。

-- 錯(cuò)誤示例：產(chǎn)生笛卡爾積
SELECT * FROM EMP, DEPT;

2. 正確多表查詢（內(nèi)連接）

必須加上關(guān)聯(lián)條件（通常是外鍵 = 主鍵）。

案例 1：員工名、工資、所在部門名

SELECT EMP.ename, EMP.sal, DEPT.dname 
FROM EMP, DEPT 
WHERE EMP.deptno = DEPT.deptno;

案例 2：只看 10 號(hào)部門的員工與部門名

SELECT ename, sal, dname 
FROM EMP, DEPT 
WHERE EMP.deptno = DEPT.deptno 
AND DEPT.deptno = 10;

案例 3：員工姓名、工資、工資等級(jí)

SELECT ename, sal, grade 
FROM EMP, SALGRADE 
WHERE EMP.sal BETWEEN losal AND hisal;

三、自連接：一張表自己連自己

自連接：同一張表起兩個(gè)別名，當(dāng)成兩張表用。典型場景：員工與領(lǐng)導(dǎo)關(guān)系（員工表的 mgr 指向領(lǐng)導(dǎo)的 empno）。

案例：查員工 FORD 的上級(jí)編號(hào)與姓名

方式 1：子查詢

SELECT empno, ename 
FROM emp 
WHERE empno = (SELECT mgr FROM emp WHERE ename='FORD');

方式 2：自連接（更優(yōu)雅）

SELECT leader.empno, leader.ename 
FROM emp leader, emp worker 
WHERE leader.empno = worker.mgr 
AND worker.ename='FORD';

要點(diǎn)：給表起別名，區(qū)分 “領(lǐng)導(dǎo)表”leader 和 “員工表”worker。

四、子查詢（嵌套查詢）：復(fù)合查詢靈魂

子查詢：把一個(gè) SELECT 嵌套在另一個(gè) SQL 里，先執(zhí)行內(nèi)層，再執(zhí)行外層。

1. 單行子查詢（返回 1 行 1 列）

用于 = > < >= <= 這類單值比較。

案例：和 SMITH 同一部門的員工

SELECT * FROM EMP 
WHERE deptno = (SELECT deptno FROM EMP WHERE ename='SMITH');

2. 多行子查詢（返回多行 1 列）

必須搭配 IN / ANY / ALL 使用。

① IN（在結(jié)果列表里）

查詢和 10 部門崗位相同，但不屬于 10 部門的員工：

SELECT ename, job, sal, deptno 
FROM emp 
WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10) 
AND deptno != 10;

② ALL（比所有都…）

工資比 30 部門所有人都高的員工：

SELECT ename, sal, deptno 
FROM EMP 
WHERE sal > ALL(SELECT sal FROM EMP WHERE deptno=30);

③ ANY（比任意一個(gè)…）

工資比 30 部門任意一人高即可：

SELECT ename, sal, deptno 
FROM EMP 
WHERE sal > ANY(SELECT sal FROM EMP WHERE deptno=30);

3. 多列子查詢（返回多列）

同時(shí)匹配多個(gè)字段，用 (字段1, 字段2) = (子查詢列1, 列2)。

案例：和 SMITH 部門、崗位完全相同的人（排除 SMITH）：

SELECT ename FROM EMP 
WHERE (deptno, job) = (SELECT deptno, job FROM EMP WHERE ename='SMITH') 
AND ename <> 'SMITH';

4. FROM 里的子查詢（臨時(shí)表 / 派生表）

把子查詢結(jié)果當(dāng)臨時(shí)表使用，非常適合先分組統(tǒng)計(jì)、再關(guān)聯(lián)查詢。

案例 1：高于本部門平均工資的員工

SELECT ename, deptno, sal, FORMAT(asal,2) 
FROM EMP, 
  (SELECT AVG(sal) asal, deptno dt FROM EMP GROUP BY deptno) tmp 
WHERE EMP.sal > tmp.asal AND EMP.deptno = tmp.dt;

案例 2：每個(gè)部門工資最高的人

SELECT EMP.ename, EMP.sal, EMP.deptno, ms 
FROM EMP, 
  (SELECT MAX(sal) ms, deptno FROM EMP GROUP BY deptno) tmp 
WHERE EMP.deptno = tmp.deptno AND EMP.sal = tmp.ms;

案例 3：部門信息 + 部門人數(shù)

SELECT DEPT.deptno, dname, mycnt, loc 
FROM DEPT, 
  (SELECT COUNT(*) mycnt, deptno FROM EMP GROUP BY deptno) tmp 
WHERE DEPT.deptno = tmp.deptno;

五、合并查詢：UNION 與 UNION ALL

把多個(gè) SELECT 結(jié)果縱向拼接，要求：

• 列數(shù)相同
• 對(duì)應(yīng)列類型兼容
• 列名以第一個(gè) SELECT 為準(zhǔn)

1. UNION：合并并自動(dòng)去重

SELECT ename, sal, job FROM EMP WHERE sal>2500
UNION
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';

2. UNION ALL：直接合并，不去重

性能比 UNION 高很多，確定無重復(fù)時(shí)優(yōu)先用它。

SELECT ename, sal, job FROM EMP WHERE sal>2500
UNION ALL
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';

對(duì)比速記

六、復(fù)合查詢核心總結(jié)

1. 多表查詢一定要加連接條件，避免笛卡爾積
2. 自連接 = 同表起別名，處理層級(jí)關(guān)系
3. 子查詢分：單行 / 多行 / 多列 / FROM 子查詢
4. IN / ANY / ALL 專門處理多行子查詢
5. UNION 去重，UNION ALL 性能更高
6. 分組后過濾用 HAVING，不是 WHERE

七、學(xué)習(xí)建議

• 先把本文案例手敲一遍
• 用 EXPLAIN 看執(zhí)行計(jì)劃，理解查詢原理
• 多刷?？?/ LeetCode SQL 專題，強(qiáng)化手感

復(fù)合查詢是 MySQL 最核心、面試最高頻的知識(shí)點(diǎn)，吃透它，你的 SQL 水平會(huì)直接上一個(gè)臺(tái)階。

最新評(píng)論