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题 考点对照题号核心考点8row_number()partition by9rank()全公司排名跳号10dense_rank()全公司排名不跳号11CTE 聚合函数 关联查询12CTE row_number() 筛选前N名13关联子查询比所在部门平均工资高14窗口函数sum() over() 百分比计算15CTE row_number()between范围筛选#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_idt11.dept_idwhereavgs8500;#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_iddepartments.dept_idwheret12.srank2;#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_ide.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 中直接使用窗口函数别名 执行顺序错误核心知识点总结窗口函数三件套sqlrow_number() over (partition by 分组列 order by 排序列 排序方式) – 唯一序号不并列rank() over (order by 排序列 排序方式) – 并列跳号dense_rank() over (order by 排序列 排序方式) – 并列不跳号函数 工资相同 下一个排名row_number() 不并列分1、2 3rank() 并列1、1 3跳号dense_rank() 并列1、1 2不跳号窗口函数的两大限制限制 说明 解决方案❌ 不能在 where 中使用 where 执行在 select 之前别名还不存在 用子查询或 CTE 包一层再筛选❌ 不能直接用于除法函数嵌套错误 用 row_number() 去除没有意义 用 salary 去除关联子查询sql– 比所在部门平均工资高select e.*from employees ewhere e.salary (select avg(salary)from employeeswhere dept_id e.dept_id – 关键用外层别名关联);要点外层一定要起别名子查询中用这个别名来关联。CTE 标准模板取前N名sqlwith 临时表名 as (select *,row_number() over (partition by 分组列 order by 排序列 desc) as rnfrom 原表)select *from 临时表名where rn N;执行顺序重要sqlfrom – 1. 确定数据源where – 2. 行级筛选此时还没有窗口函数group by – 3. 分组having – 4. 组级筛选select – 5. 选择列窗口函数在这里生成order by – 6. 排序所以where 中不能引用窗口函数别名因为窗口函数在 select 阶段才生成。逗号规则老问题sqlselect 列1, – ✅ 后面还有列加逗号列2, – ✅ 后面还有列加逗号列3 – ✅ 最后一项不加逗号from 表;换行不等于分隔逗号才是分隔符总结窗口函数三兄弟rank 跳号 dense 连row_number 唯一序where 里面不能用子查询或 CTE 来包关联子查询要记牢外层别名内层用逗号分隔最后不加换行不能代替它。