实战：多表查询综合
实战：多表查询综合
📋 项目需求
本节通过一个电商订单管理系统的实战场景，综合运用前面学到的多表查询技能。
数据库表结构
我们使用统一的 4 表数据库：
SQL
-- 部门表
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL,
city VARCHAR(50),
budget DECIMAL(12,2)
);
-- 员工表
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100),
salary DECIMAL(10,2),
department_id INT,
hire_date DATE,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- 订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
employee_id INT,
order_date DATE,
total_amount DECIMAL(12,2),
status VARCHAR(20),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
-- 产品表
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
stock INT,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
测试数据
SQL
-- 部门数据
INSERT INTO departments VALUES (1, '技术部', '北京', 500000);
INSERT INTO departments VALUES (2, '销售部', '上海', 300000);
INSERT INTO departments VALUES (3, '市场部', '广州', 200000);
INSERT INTO departments VALUES (4, '财务部', '深圳', 150000);
-- 员工数据
INSERT INTO employees VALUES (1, '张', '伟', 'zhangwei@co.com', 12000, 1, '2023-03-15');
INSERT INTO employees VALUES (2, '李', '娜', 'lina@co.com', 9500, 1, '2023-06-20');
INSERT INTO employees VALUES (3, '王', '强', 'wangqiang@co.com', 8000, 2, '2022-11-10');
INSERT INTO employees VALUES (4, '赵', '敏', 'zhaomin@co.com', 11000, 2, '2024-01-05');
INSERT INTO employees VALUES (5, '刘', '洋', 'liuyang@co.com', 7500, 3, '2024-04-18');
INSERT INTO employees VALUES (6, '陈', '静', 'chenjing@co.com', 9000, NULL, '2025-02-28');
-- 订单数据
INSERT INTO orders VALUES (1001, 101, 3, '2026-05-10', 2500.00, 'completed');
INSERT INTO orders VALUES (1002, 102, 3, '2026-05-15', 1800.00, 'completed');
INSERT INTO orders VALUES (1003, 103, 4, '2026-06-01', 3200.00, 'shipped');
INSERT INTO orders VALUES (1004, 101, 1, '2026-06-05', 950.00, 'pending');
INSERT INTO orders VALUES (1005, 104, 4, '2026-06-10', 4100.00, 'completed');
INSERT INTO orders VALUES (1006, 105, 3, '2026-06-15', 2800.00, 'cancelled');
-- 产品数据
INSERT INTO products VALUES (1, '笔记本电脑', 6999.00, 50, 1);
INSERT INTO products VALUES (2, '无线鼠标', 129.00, 200, 1);
INSERT INTO products VALUES (3, '办公桌', 899.00, 30, 2);
INSERT INTO products VALUES (4, '投影仪', 3500.00, 15, 3);
INSERT INTO products VALUES (5, '打印机', 2200.00, 25, 1);
🏗️ 查询实战
查询一：员工订单业绩报表
需求：查看每位员工的订单数量和总销售额，包含没有订单的员工。
SQL
SELECT
e.employee_id,
CONCAT(e.first_name, e.last_name) AS 姓名,
d.department_name AS 部门,
COUNT(o.order_id) AS 订单数量,
COALESCE(SUM(o.total_amount), 0) AS 总销售额
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY 总销售额 DESC;
执行结果：
TEXT
employee_id | 姓名 | 部门 | 订单数量 | 总销售额
-----------+--------+--------+----------+----------
4 | 赵敏 | 销售部 | 2 | 7300.00
3 | 王强 | 销售部 | 3 | 7100.00
1 | 张伟 | 技术部 | 1 | 950.00
2 | 李娜 | 技术部 | 0 | 0.00
5 | 刘洋 | 市场部 | 0 | 0.00
6 | 陈静 | NULL | 0 | 0.00
代码解读：
LEFT JOIN departments— 关联部门表，获取部门名称，保留没有部门的员工LEFT JOIN orders— 关联订单表，保留没有订单的员工COALESCE(SUM(...), 0)— 将 NULL 转换为 0，避免显示为空GROUP BY— 按员工分组，聚合计算订单数量和总金额
查询二：各部门销售排名
需求：按部门统计总销售额，并进行排名。
SQL
SELECT
d.department_name AS 部门,
d.city AS 城市,
COUNT(DISTINCT e.employee_id) AS 员工数,
COUNT(o.order_id) AS 订单总数,
COALESCE(SUM(o.total_amount), 0) AS 总销售额,
RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS 排名
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY d.department_id, d.department_name, d.city
ORDER BY 总销售额 DESC;
代码解读：
LEFT JOIN链式连接：departments → employees → ordersCOUNT(DISTINCT e.employee_id)— 去重计数员工，避免因多条订单重复计算RANK()— 窗口函数，按总销售额排名COALESCE— 处理没有订单的部门，显示为 0
查询三：高薪员工及其处理的订单详情
需求：找出工资高于公司平均工资的员工，以及他们处理的订单信息。
SQL
SELECT
e.employee_id,
CONCAT(e.first_name, e.last_name) AS 姓名,
e.salary AS 工资,
o.order_id,
o.order_date AS 订单日期,
o.total_amount AS 订单金额,
o.status AS 订单状态
FROM employees e
INNER JOIN orders o ON e.employee_id = o.employee_id
WHERE e.salary > (
SELECT AVG(salary) FROM employees
)
ORDER BY e.salary DESC, o.order_date;
代码解读：
- 子查询
(SELECT AVG(salary) FROM employees)— 计算公司平均工资 INNER JOIN— 只保留有订单的高薪员工WHERE e.salary > (...)— 用子查询结果做过滤条件ORDER BY先按工资降序，再按日期升序
查询四：有订单但无对应部门产品的交叉查询
需求：找出员工所在部门没有产品的订单。
SQL
SELECT
o.order_id,
CONCAT(e.first_name, e.last_name) AS 员工姓名,
d.department_name AS 部门,
o.total_amount AS 订单金额
FROM orders o
INNER JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NOT NULL
AND e.department_id NOT IN (
SELECT DISTINCT department_id FROM products WHERE department_id IS NOT NULL
);
代码解读：
INNER JOIN employees— 关联订单和员工LEFT JOIN departments— 获取部门名称NOT IN (子查询)— 排除有产品的部门- 综合运用了 JOIN 和子查询的混合过滤
查询五：使用 UNION 合并多维度数据
需求：生成一份综合名录，合并"有订单的员工"和"所有部门"的信息。
SQL
-- 有完成订单的员工
SELECT
CONCAT(first_name, last_name) AS 名称,
'员工' AS 类型,
CAST(salary AS VARCHAR) AS 详情
FROM employees
WHERE employee_id IN (
SELECT DISTINCT employee_id FROM orders WHERE status = 'completed'
)
UNION ALL
-- 所有部门
SELECT
department_name,
'部门',
CONCAT('预算: ', CAST(budget AS VARCHAR))
FROM departments
ORDER BY 类型, 名称;
代码解读：
- 第一个 SELECT — 用子查询筛选有完成订单的员工
- 第二个 SELECT — 获取所有部门
UNION ALL— 合并两个结果集，保留所有行- 添加常量列"类型"区分数据来源
查询六：综合分析 — 订单状态分布与员工绩效
需求：统计每个员工的订单状态分布，找出处理过"已完成"订单的员工。
SQL
SELECT
CONCAT(e.first_name, e.last_name) AS 员工姓名,
d.department_name AS 部门,
SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS 已完成,
SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS 已发货,
SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS 待处理,
SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS 已取消,
COUNT(o.order_id) AS 总订单数
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
WHERE e.employee_id IN (
SELECT DISTINCT employee_id FROM orders
)
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY 总订单数 DESC;
代码解读：
CASE WHEN条件聚合 — 将行数据转换为列（行转列）LEFT JOIN保留所有关联数据- 子查询
IN (...)过滤掉没有订单的员工 GROUP BY按员工分组统计
❓ 常见问题
Q：什么时候用 INNER JOIN，什么时候用 LEFT JOIN？ A： 如果你需要保留左表的所有记录（即使右表没有匹配），用
LEFT JOIN。如果只需要两表都匹配的记录，用INNER JOIN。实战中，报表类查询通常用LEFT JOIN，数据校验类用INNER JOIN。
Q：子查询和 JOIN 哪个性能更好？ A： 现代数据库优化器通常会将子查询转换为 JOIN 执行。简单关联用
JOIN更直观；存在性检查（"是否存在"）用EXISTS或IN子查询更语义化。性能差异需用EXPLAIN验证。
Q：如何调试复杂的多表查询？ A： 建议逐步构建：先写最内层的子查询，确认结果正确后，再一层层包裹。也可以用 CTE（
WITH语句）将复杂查询拆分为多个可读的临时结果集。
Q：GROUP BY 后的 SELECT 只能写聚合函数和分组列吗？ A： 是的。
SELECT中出现的非聚合列必须全部出现在GROUP BY中，否则数据库会报错。这是 SQL 标准的要求。
📖 小节
| 技能 | 本节应用 |
|---|---|
| INNER JOIN | 关联有匹配关系的表 |
| LEFT JOIN | 保留左表全部记录 |
| 子查询 (WHERE IN) | 作为过滤条件 |
| 子查询 (标量) | 返回单个值用于比较 |
| UNION ALL | 合并不同维度的结果集 |
| CASE WHEN | 条件聚合（行转列） |
| 聚合函数 | COUNT、SUM、AVG、COALESCE |
| 窗口函数 | RANK() 排名 |
实战要点：
- 复杂查询从简单到复杂逐步构建
- 先确保单表查询正确，再多表关联
- 使用
COALESCE处理 NULL 值 - 用
EXPLAIN分析查询性能
📝 作业
- 编写查询：找出工资最高的员工所在的部门信息，包括部门名称、城市和预算。
- 编写查询：统计每个城市的部门数量和员工总数，使用
LEFT JOIN确保没有员工的城市也显示。 - 编写查询：找出从未处理过订单的员工，使用子查询（
NOT IN）和LEFT JOIN ... IS NULL两种方式实现，对比结果是否一致。 - 编写查询：将员工按工资分为"高薪（>10000）"、"中薪（6000-10000）"、"低薪（<6000）"三组，统计每组人数和平均工资。
下一课
下一节我们将学习 聚合函数 — 深入掌握 COUNT、SUM、AVG、MAX、MIN 以及 GROUP BY 和 HAVING 的高级用法。
