MySQL多表查询与优化实战：牛客网21-30题解析

1. 牛客网MySQL刷题实战指南（21-30题解析）

作为一名常年与数据库打交道的开发者，我深知SQL能力是程序员的核心竞争力之一。牛客网的MySQL题库覆盖了从基础到进阶的各类场景，其中21-30题这个区间的题目特别能检验我们对多表查询、聚合函数和复杂条件的掌握程度。今天我就带大家逐题拆解这些高频考题，分享我的解题思路和实战技巧。

2. 题目解析与核心知识点

2.1 第21题：多表连接基础

这道题考察的是最基本的INNER JOIN操作，要求从employees和departments表中查询员工及其所属部门名称。看似简单，但有几个关键点需要注意：

SELECT e.emp_no, e.first_name, d.dept_name FROM employees e INNER JOIN dept_emp de ON e.emp_no = de.emp_no INNER JOIN departments d ON de.dept_no = d.dept_no

注意：实际业务中表连接可能产生笛卡尔积，建议养成使用表别名的习惯，同时明确指定JOIN条件避免漏写ON子句。

2.2 第22题：LEFT JOIN的特殊性

这道题要求查询所有员工信息，包括没有部门的员工。这里必须使用LEFT JOIN而非INNER JOIN：

SELECT e.emp_no, e.first_name, d.dept_name FROM employees e LEFT JOIN dept_emp de ON e.emp_no = de.emp_no LEFT JOIN departments d ON de.dept_no = d.dept_no

我在实际项目中踩过的坑：当右表存在多条匹配记录时，LEFT JOIN会导致左表记录重复。这时需要配合DISTINCT或子查询处理。

2.3 第23题：GROUP BY与聚合函数

这道题需要计算每个部门的员工人数，典型的分组统计场景：

SELECT d.dept_no, d.dept_name, COUNT(*) as emp_count FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no GROUP BY d.dept_no, d.dept_name

经验分享：

1. GROUP BY子句必须包含所有非聚合字段
2. MySQL5.7+默认开启ONLY_FULL_GROUP_BY模式，不符合规范的SQL会报错
3. 大数据量时，考虑在dept_no字段上建立索引提升性能

3. 进阶查询技巧

3.1 第24题：HAVING筛选分组结果

在分组后筛选员工数大于5的部门，需要用到HAVING：

SELECT d.dept_no, COUNT(*) as emp_count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no GROUP BY d.dept_no HAVING COUNT(*) > 5

与WHERE的区别：

• WHERE在分组前过滤，不能使用聚合函数
• HAVING在分组后过滤，可以使用聚合函数
• 性能考虑：优先用WHERE减少处理的数据量

3.2 第25题：子查询应用

查询比平均工资高的员工信息，需要先计算平均值再比较：

SELECT emp_no, salary FROM salaries WHERE salary > (SELECT AVG(salary) FROM salaries)

优化建议：

1. 子查询结果会被重复计算，大数据量时可考虑使用变量存储中间结果
2. 对于关联子查询，注意索引的使用情况

3.3 第26题：日期函数处理

查询入职超过5年的员工，需要用到日期函数：

SELECT emp_no, hire_date FROM employees WHERE DATEDIFF(NOW(), hire_date) > 365*5

日期处理的常见坑点：

1. 不同数据库的日期函数差异（MySQL的DATEDIFF、SQL Server的DATEADD等）
2. 时区问题可能导致日期计算错误
3. 考虑使用索引友好的写法：hire_date < DATE_SUB(NOW(), INTERVAL 5 YEAR)

4. 复杂查询实战

4.1 第27题：多条件CASE WHEN

根据薪资水平给员工打标签：

SELECT emp_no, salary, CASE WHEN salary < 60000 THEN '初级' WHEN salary BETWEEN 60000 AND 100000 THEN '中级' ELSE '高级' END AS level FROM salaries

CASE WHEN的高级用法：

1. 可以在SELECT、WHERE、ORDER BY等子句中使用
2. 支持嵌套和多条件判断
3. 与聚合函数结合实现复杂统计

4.2 第28题：窗口函数应用

查询每个部门薪资排名前三的员工（MySQL8.0+）：

WITH dept_salary_rank AS ( SELECT de.emp_no, d.dept_name, s.salary, RANK() OVER (PARTITION BY de.dept_no ORDER BY s.salary DESC) as rnk FROM dept_emp de JOIN salaries s ON de.emp_no = s.emp_no JOIN departments d ON de.dept_no = d.dept_no ) SELECT * FROM dept_salary_rank WHERE rnk <= 3

窗口函数要点：

1. 执行顺序在WHERE和GROUP BY之后
2. PARTITION BY类似GROUP BY但不减少行数
3. 不同排序函数区别：RANK()、DENSE_RANK()、ROW_NUMBER()

4.3 第29题：自连接查询

查询薪资相同的员工对：

SELECT DISTINCT s1.emp_no as emp1, s2.emp_no as emp2, s1.salary FROM salaries s1 JOIN salaries s2 ON s1.salary = s2.salary AND s1.emp_no < s2.emp_no

自连接注意事项：

1. 必须使用不同别名区分同一表的多个实例
2. 条件s1.emp_no < s2.emp_no避免重复组合
3. 大数据量时性能较差，考虑其他实现方式

4.4 第30题：复杂嵌套查询

查询每个部门最高薪资的员工信息：

SELECT d.dept_no, e.emp_no, e.first_name, s.salary FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no JOIN employees e ON de.emp_no = e.emp_no JOIN salaries s ON e.emp_no = s.emp_no WHERE (d.dept_no, s.salary) IN ( SELECT de.dept_no, MAX(s.salary) FROM dept_emp de JOIN salaries s ON de.emp_no = s.emp_no GROUP BY de.dept_no )

优化方案对比：

1. 使用JOIN替代IN子查询
2. 使用窗口函数简化逻辑
3. 临时表存储中间结果

5. 性能优化与实战技巧

5.1 索引使用原则

1. 为所有JOIN条件和WHERE条件中的字段建立索引
2. 多列索引遵循最左前缀原则
3. EXPLAIN分析执行计划，关注type列（最好达到ref级别）

5.2 查询重构技巧

1. 将OR条件改写为UNION ALL
2. 避免在WHERE子句中对字段进行函数操作
3. 分页查询使用延迟关联优化

5.3 事务与锁机制

1. 复杂查询考虑事务隔离级别的影响
2. 大批量操作分批处理避免长事务
3. 注意间隙锁对并发性能的影响

6. 常见错误排查

错误现象	可能原因	解决方案
结果集异常	JOIN条件漏写或错误	检查ON子句，使用别名明确字段归属
分组结果不对	GROUP BY字段不全	包含所有非聚合字段
性能突然下降	索引失效或统计信息过期	ANALYZE TABLE更新统计信息
排序不一致	未指定ORDER BY	明确排序条件，考虑使用索引排序

7. 刷题进阶建议

1. 建立错题本记录易错点
2. 对每道题尝试多种解法
3. 结合实际业务场景思考题目变形
4. 定期复习高频考点

我在带团队时发现，很多开发者能写出正确的SQL，但往往不了解执行计划和性能特征。建议在牛客网刷题时，不仅要追求答案正确，还要养成用EXPLAIN分析查询的习惯。比如第28题的窗口函数解法，在MySQL8.0之前版本需要用复杂的自连接实现，了解这些演进过程能加深我们对SQL的理解。