前言：

在實(shí)際開發(fā)中，單表查詢遠(yuǎn)不能滿足復(fù)雜業(yè)務(wù)需求 —— 員工信息散落在員工表、部門表、薪資等級(jí)表中，需要跨表關(guān)聯(lián)才能獲取完整數(shù)據(jù)；統(tǒng)計(jì)分析時(shí)需嵌套查詢篩選條件；多結(jié)果集合并需用到集合操作符。本文全面拆解 MySQL 復(fù)合查詢的核心玩法，包括多表查詢、自連接、子查詢、合并查詢，所有 SQL 均采用小寫形式，貼合開發(fā)規(guī)范，附帶實(shí)戰(zhàn)案例和避坑要點(diǎn)

一. 基礎(chǔ)回顧：復(fù)合查詢的前置知識(shí)

在學(xué)習(xí)復(fù)雜復(fù)合查詢前，先回顧基礎(chǔ)查詢的核心語法，為后續(xù)進(jìn)階打基礎(chǔ)：

-- 1. 條件篩選：工資>500或崗位為manager，且姓名首字母為J
select * from emp where (sal>500 or job='manager') and ename like 'j%';
-- 2. 多字段排序：部門號(hào)升序，工資降序
select * from emp order by deptno asc, sal desc;
-- 3. 計(jì)算字段+排序：年薪（sal*12+補(bǔ)貼）降序
select ename, sal*12+ifnull(comm,0) as 年薪 from emp order by 年薪 desc;
-- 4. 聚合查詢+篩選：各部門平均工資（保留2位小數(shù)）和最高工資
select deptno, format(avg(sal),2) as 平均工資, max(sal) as 最高工資 from emp group by deptno;
-- 5. having篩選聚合結(jié)果：平均工資低于2000的部門
select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;

二. 多表查詢：跨表關(guān)聯(lián)核心玩法

多表查詢是復(fù)合查詢的基礎(chǔ)，用于從多個(gè)關(guān)聯(lián)表中提取數(shù)據(jù)，核心是通過 “關(guān)聯(lián)字段” 消除笛卡爾積（無關(guān)聯(lián)條件時(shí)，表 1 所有行與表 2 所有行組合，數(shù)據(jù)量爆炸）。

2.1 測(cè)試表結(jié)構(gòu)

本次實(shí)戰(zhàn)基于 3 張經(jīng)典表，先明確表結(jié)構(gòu)和關(guān)聯(lián)關(guān)系：

• emp（員工表）：存儲(chǔ)員工基本信息，關(guān)聯(lián)字段deptno（關(guān)聯(lián)部門表）、sal（關(guān)聯(lián)薪資等級(jí)表）；
• dept（部門表）：存儲(chǔ)部門信息，關(guān)聯(lián)字段deptno；
• salgrade（薪資等級(jí)表）：存儲(chǔ)薪資等級(jí)規(guī)則，關(guān)聯(lián)字段losal（最低工資）、hisal（最高工資）。

2.2 多表查詢核心語法

select 表1.字段, 表2.字段 
from 表1, 表2 
where 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段 [and 其他篩選條件];

2.3 實(shí)戰(zhàn)案例

2.3.1 關(guān)聯(lián)兩張表：?jiǎn)T工 + 部門信息

需求：查詢員工姓名、工資及所在部門名稱

-- 核心：通過deptno關(guān)聯(lián)emp和dept表，消除笛卡爾積
select emp.ename, emp.sal, dept.dname 
from emp, dept 
where emp.deptno = dept.deptno;

2.3.2 多表 + 條件篩選：指定部門員工信息

需求：查詢 10 號(hào)部門的員工姓名、工資及部門名稱

select emp.ename, emp.sal, dept.dname 
from emp, dept 
where emp.deptno = dept.deptno 
  and dept.deptno = 10; -- 篩選10號(hào)部門

2.3.3 關(guān)聯(lián)多張表：?jiǎn)T工 + 薪資等級(jí)

需求：查詢員工姓名、工資及對(duì)應(yīng)的薪資等級(jí)

select emp.ename, emp.sal, salgrade.grade 
from emp, salgrade 
where emp.sal between salgrade.losal and salgrade.hisal; -- 工資在薪資等級(jí)區(qū)間內(nèi)

2.4 多表查詢避坑點(diǎn)

• 必須加關(guān)聯(lián)條件：無關(guān)聯(lián)條件會(huì)產(chǎn)生笛卡爾積（如 emp 有 14 行、dept 有 4 行，會(huì)產(chǎn)生 14×4=56 行無效數(shù)據(jù)）；
• 字段歧義需加表名前綴：若多個(gè)表有同名字段（如deptno），需用表名.字段區(qū)分；
• 關(guān)聯(lián)字段類型必須一致：emp.deptno 和 dept.deptno 需同為 int 類型，否則關(guān)聯(lián)失效。

三. 自連接：同表關(guān)聯(lián)查詢

自連接是多表查詢的特殊形式 —— 將同一張表當(dāng)作兩張表使用，通過別名區(qū)分，適用于查詢表內(nèi)關(guān)聯(lián)數(shù)據(jù)（如員工與上級(jí)領(lǐng)導(dǎo)的關(guān)系）。

3.1 核心語法

select 表別名1.字段, 表別名2.字段 
from 表 表別名1, 表 表別名2 
where 表別名1.關(guān)聯(lián)字段 = 表別名2.關(guān)聯(lián)字段 [and 篩選條件];

3.2 實(shí)戰(zhàn)案例：查詢員工的上級(jí)領(lǐng)導(dǎo)

需求：查詢員工 ford 的上級(jí)領(lǐng)導(dǎo)編號(hào)和姓名（emp 表中mgr字段是領(lǐng)導(dǎo)的empno）

-- 方法1：子查詢（簡(jiǎn)單場(chǎng)景）
select empno, ename from emp 
where empno = (select mgr from emp where ename='ford');
-- 方法2：自連接（復(fù)雜場(chǎng)景更靈活）
select leader.empno as 領(lǐng)導(dǎo)編號(hào), leader.ename as 領(lǐng)導(dǎo)姓名 
from emp leader, emp worker -- leader=領(lǐng)導(dǎo)表，worker=員工表
where leader.empno = worker.mgr -- 領(lǐng)導(dǎo)編號(hào)=員工的上級(jí)編號(hào)
  and worker.ename = 'ford'; -- 篩選員工為ford

3.3 自連接關(guān)鍵技巧

• 必須給表起不同別名（如leader、worker），否則 MySQL 無法區(qū)分兩張 “虛擬表”；
• 關(guān)聯(lián)字段需是表內(nèi)的關(guān)聯(lián)關(guān)系（如員工表的mgr與自身的empno）。

四. 子查詢：嵌套查詢的靈活用法

子查詢（嵌套查詢）是指嵌入在其他 SQL 語句中的 select 語句，按返回結(jié)果可分為單行、多行、多列子查詢，按位置可分為 where 子句、from 子句中的子查詢。

4.1 單行子查詢：返回 1 行 1 列結(jié)果

適用于篩選條件為 “等于、大于、小于” 單個(gè)值的場(chǎng)景，常用比較運(yùn)算符（=、>、<、>=、<=）。

實(shí)戰(zhàn)案例：
需求：查詢與 smith 同部門的所有員工（不含 smith）

select * from emp 
where deptno = (select deptno from emp where ename='smith') -- 子查詢返回smith的部門號(hào)
  and ename != 'smith'; -- 排除smith本人

4.2 多行子查詢：返回多行 1 列結(jié)果

適用于篩選條件為 “在多個(gè)值中”“大于所有值”“大于任意值” 的場(chǎng)景，常用關(guān)鍵字in、all、any。

4.2.1 in 關(guān)鍵字：匹配多個(gè)值中的任意一個(gè)

需求：查詢和 10 號(hào)部門崗位相同，但不屬于 10 號(hào)部門的員工

select ename, job, sal, deptno from emp 
where job in (select distinct job from emp where deptno=10) -- 子查詢返回10號(hào)部門的所有崗位
  and deptno != 10; -- 排除10號(hào)部門

4.2.2 all 關(guān)鍵字：大于 / 小于所有值

需求：查詢工資比 30 號(hào)部門所有員工工資都高的員工

select ename, sal, deptno from emp 
where sal > all(select sal from emp where deptno=30); -- 工資>30號(hào)部門所有員工工資

4.2.3 any 關(guān)鍵字：大于 / 小于任意一個(gè)值

需求：查詢工資比 30 號(hào)部門任意員工工資高的員工（含自身部門）

select ename, sal, deptno from emp 
where sal > any(select sal from emp where deptno=30); -- 工資>30號(hào)部門至少一個(gè)員工工資

4.3 多列子查詢：返回多行多列結(jié)果

適用于篩選條件需匹配 “多個(gè)字段組合” 的場(chǎng)景，子查詢返回多列，主查詢用括號(hào)接收字段組合。

實(shí)戰(zhàn)案例：
需求：查詢與 smith 部門和崗位完全相同的員工（不含 smith）

select ename from emp 
where (deptno, job) = (select deptno, job from emp where ename='smith') -- 匹配部門+崗位組合
  and ename != 'smith';

4.4 from 子句中的子查詢：臨時(shí)表用法

將子查詢結(jié)果當(dāng)作 “臨時(shí)表”，用于復(fù)雜統(tǒng)計(jì)分析（如先聚合再關(guān)聯(lián)），核心是給臨時(shí)表起別名。

實(shí)戰(zhàn)案例 1：查詢高于本部門平均工資的員工

select emp.ename, emp.deptno, emp.sal, format(tmp.asal,2) as 部門平均工資 
from emp, 
     (select avg(sal) as asal, deptno as dt from emp group by deptno) tmp -- 臨時(shí)表：各部門平均工資
where emp.deptno = tmp.dt -- 員工部門=臨時(shí)表部門
  and emp.sal > tmp.asal; -- 員工工資>部門平均工資

實(shí)戰(zhàn)案例 2：查詢各部門工資最高的員工

select emp.ename, emp.sal, emp.deptno, tmp.ms as 部門最高工資 
from emp, 
     (select max(sal) as ms, deptno from emp group by deptno) tmp -- 臨時(shí)表：各部門最高工資
where emp.deptno = tmp.deptno 
  and emp.sal = tmp.ms;

4.5 子查詢避坑指南

• 單行子查詢只能用單行運(yùn)算符：若子查詢返回多行，不能用=，需用in；
• from 子句的子查詢必須起別名：MySQL 要求臨時(shí)表必須有別名，否則報(bào)錯(cuò)；
• 子查詢盡量簡(jiǎn)化：復(fù)雜子查詢可拆分為臨時(shí)表或多步查詢，提升可讀性和性能。

五. 合并查詢：union 與 union all

合并查詢用于將多個(gè) select 語句的結(jié)果集合并為一個(gè)，適用于多條件獨(dú)立查詢后合并結(jié)果的場(chǎng)景，核心是union（去重）和union all（不去重）。

5.1 核心語法

-- 去重合并（自動(dòng)刪除重復(fù)行）
select 字段 from 表1 where 條件
union
select 字段 from 表2 where 條件;
-- 不去重合并（保留重復(fù)行，性能更優(yōu)）
select 字段 from 表1 where 條件
union all
select 字段 from 表2 where 條件;

5.2 實(shí)戰(zhàn)案例

案例 1：union 去重合并
需求：查詢工資 > 2500 或崗位為 manager 的員工（去重）

select ename, sal, job from emp where sal>2500
union -- 自動(dòng)去重（manager中工資>2500的員工只顯示一次）
select ename, sal, job from emp where job='manager';

案例 2：union all 不去重合并
需求：查詢工資 > 2500 或崗位為 manager 的員工（保留重復(fù)）

select ename, sal, job from emp where sal>2500
union all -- 保留重復(fù)行（manager中工資>2500的員可能會(huì)顯示多次）
select ename, sal, job from emp where job='manager';

六. 實(shí)戰(zhàn) OJ 真題：復(fù)合查詢落地應(yīng)用

結(jié)合?？途W(wǎng)經(jīng)典 OJ 題，練習(xí)復(fù)合查詢的實(shí)際應(yīng)用：
真題 1：查找所有員工入職時(shí)的薪水情況（emp_no+salary，逆序）

select e.emp_no, s.salary 
from employees e, salaries s 
where e.emp_no = s.emp_no 
  and s.from_date = e.hire_date 
order by e.emp_no desc;

真題 2：生成所有表的 count 查詢語句

select concat('select count(*) from ', table_name, ';') as count_sql 
from information_schema.tables 
where table_schema = 'your_database_name'; -- 替換為你的數(shù)據(jù)庫名

真題 3：獲取所有非 manager 的員工 emp_no

select emp_no from employees
where emp_no not in(select emp_no from dept_manager);

真題 4：獲取所有員工當(dāng)前的 manager（排除 manager 是自己的情況）

select e.emp_no, d.emp_no as manager 
from dept_emp as e, dept_manager as d 
where e.dept_no = d.dept_no 
  and e.emp_no != d.emp_no;

七. 總結(jié)

MySQL 復(fù)合查詢是解決復(fù)雜業(yè)務(wù)需求的核心，核心要點(diǎn)總結(jié)：

• 多表查詢：通過關(guān)聯(lián)字段消除笛卡爾積，適用于跨表提取數(shù)據(jù)；
• 自連接：同表當(dāng)作兩張表，適用于表內(nèi)關(guān)聯(lián)（如員工與領(lǐng)導(dǎo)）；
• 子查詢：嵌套在 where/from 子句中，靈活篩選和統(tǒng)計(jì)，需注意單行 / 多行匹配規(guī)則；
• 合并查詢：union（去重）和 union all（不去重），適用于多結(jié)果集合并；
• 避坑關(guān)鍵：關(guān)聯(lián)字段一致、臨時(shí)表起別名、優(yōu)先選擇高效語法（如 union all 替代 union）。

到此這篇關(guān)于MySQL 復(fù)合查詢核心指南之多表、子查詢與實(shí)戰(zhàn)技巧的文章就介紹到這了,更多相關(guān)mysql多表、子查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論