MySQL复盘②

第8-15题 题目原文

8.使用窗口函数row_number()，查询每个部门中按工资降序排列的员工排名。

9.使用窗口函数rank()，查询所有员工按工资降序的排名。

10.使用窗口函数dense_rank()，查询所有员工按工资降序的排名。

11.使用 CTE，先查询出每个部门的平均工资，再查询平均工资高于 8500 的部门及其平均工资。

12.使用 CTE，查询每个部门工资排名前 2 的员工（按工资降序）。

13.查询工资比所在部门平均工资高的员工姓名、工资和部门编号（使用子查询作为条件）。

14.使用窗口函数，查询每个部门的员工按工资降序排列，并计算该员工工资占部门总工资的百分比。

15.使用窗口函数row_number()，查询整个公司工资排名第 3 到第 5 名的员工姓名、工资和排名。

第8-15题 考点对照

#8selectemployees.emp_name,employees.dept_id,employees.salary,row_number()over(partitionbydept_idorderbysalarydesc)as'排行'fromemployees;#9selectemp_name,employees.salary,rank()over(orderbysalarydesc)as'排行'fromemployees;#10selectemp_name,employees.salary,dense_rank()over(orderbysalarydesc)as'排行'fromemployees;#11/* with 临时表名 as ( 子查询语句 ) select * from 临时表名; *//*子查询 平均工资 select dept_id, avg(employees.salary) as avgs from employees group by dept_id; */witht11as(selectdept_id,avg(employees.salary)asavgsfromemployeesgroupbydept_id)selectdept_name,avgsfromt11joindepartmentsondepartments.dept_id=t11.dept_idwhereavgs>8500;#12/* select *, row_number() over (partition by employees.dept_id order by employees.salary desc ) as srank from employees; *//* with t12 as ( select *, row_number() over (partition by employees.dept_id order by employees.salary desc ) as srank from employees ) select * from t12 where srank <= 2; */witht12as(selectdept_id,employees.emp_name,employees.salary,row_number()over(partitionbyemployees.dept_idorderbyemployees.salarydesc)assrankfromemployees)selectt12.emp_name,t12.salary,dept_namefromt12joindepartmentsont12.dept_id=departments.dept_idwheret12.srank<=2;#13/* 大于公司平均工资 select emp_name,salary,dept_id from employees where salary > (select avg(employees.salary) as savg from employees); */#部门佼佼者selecte.emp_name,e.salary,e.dept_idfromemployees ewheree.salary>(selectavg(salary)fromemployeeswheredept_id=e.dept_id);#14select*,row_number()over(partitionbydept_idorderbysalarydesc)assrank,sum(salary)over(partitionbydept_id)as总和,concat(round(salary/sum(salary)over(partitionbydept_id),2)*100,'%')as占比fromemployees e;#15/*错误写法 where 不允许聚合函数 应该使用子查询 select e.name,e.salary row_number() over (order by salary desc ) as srank from employees e where e.srank between 3 and 5; */select*,row_number()over(orderbysalarydesc)assrankfromemployees;selectt.emp_name,t.salary,t.srankfrom(select*,row_number()over(orderbysalarydesc)assrankfromemployees)astwheret.srankbetween3and5;

卡壳点汇总

题号 出错点 错误类型
8 别名 '排行 缺少右引号 语法错误
9 别名 '排行 缺少右引号 语法错误
10 dense_rank() 没有加 over() 和 order by 语法错误
11 group by 写在 from 前面 语法顺序错误
11 分号放错位置，导致 group by 变成独立语句 语法错误
11 关联条件写错（自己=自己） 逻辑错误
12 误用 group by 取前N名 思路错误
13 子查询中 dept_id 没有用外层别名区分 关联子查询错误
14 row_number() 和 sum() 之间缺少逗号 语法错误
14 over(pa) 没写完 语法错误
14 用 row_number() 去除算百分比 逻辑错误
15 select 中列之间缺少逗号 语法错误
15 where 中直接使用窗口函数别名 执行顺序错误

核心知识点总结

1. 窗口函数三件套
   sql
   row_number() over (partition by 分组列 order by 排序列 排序方式) – 唯一序号，不并列
   rank() over (order by 排序列 排序方式) – 并列跳号
   dense_rank() over (order by 排序列 排序方式) – 并列不跳号
   函数 工资相同 下一个排名
   row_number() 不并列（分1、2） 3
   rank() 并列（1、1） 3（跳号）
   dense_rank() 并列（1、1） 2（不跳号）

2. 窗口函数的两大限制
   限制 说明 解决方案
   ❌ 不能在 where 中使用 where 执行在 select 之前，别名还不存在 用子查询或 CTE 包一层再筛选
   ❌ 不能直接用于除法（函数嵌套错误） 用 row_number() 去除没有意义 用 salary 去除

3. 关联子查询
   sql
   – 比所在部门平均工资高
   select e.*
   from employees e
   where e.salary > (
   select avg(salary)
   from employees
   where dept_id = e.dept_id – 关键：用外层别名关联
   );
   要点：外层一定要起别名，子查询中用这个别名来关联。

4. CTE 标准模板（取前N名）
   sql
   with 临时表名 as (
   select *,
   row_number() over (partition by 分组列 order by 排序列 desc) as rn
   from 原表
   )
   select *
   from 临时表名
   where rn <= N;

5. 执行顺序（重要！）
   sql
   from – 1. 确定数据源
   where – 2. 行级筛选（此时还没有窗口函数！）
   group by – 3. 分组
   having – 4. 组级筛选
   select – 5. 选择列（窗口函数在这里生成）
   order by – 6. 排序
   所以：where 中不能引用窗口函数别名，因为窗口函数在 select 阶段才生成。

6. 逗号规则（老问题）
   sql
   select 列1, – ✅ 后面还有列，加逗号
   列2, – ✅ 后面还有列，加逗号
   列3 – ✅ 最后一项，不加逗号
   from 表;
   换行不等于分隔，逗号才是分隔符！

总结

窗口函数三兄弟，rank 跳号 dense 连，row_number 唯一序；
where 里面不能用，子查询或 CTE 来包；
关联子查询要记牢，外层别名内层用；
逗号分隔最后不加，换行不能代替它。