从哔哩哔哩罗昊学习jackfrued 老师的网课和黑马网课。
简单排序——查询最大值、次大只
-
员工表包括员工号码、姓名、工资、职位、补贴 、部门等信息。查询薪酬最高的员工姓名和薪酬价值。
做法①：最常见的子查询，先查出最大工资值，再筛选出员工表中的工资值等于这个数字的员工。
做法②：limit 做法。先按工资降序排序所有员工，再只排序 limit 1 第一条员工信息。这种做法的局限性在于 limit 我在介绍的时候也写过，就是当最值不止一个的时候，这种方法只能找到一个员工。
做法③：
select `ename`,`salary` from `tb_employee` t1 where( select count(*) from `tb_employee` t2 where `t2`.`salary`>`t1`.`salary` )=0;-- 结尾改成1，工资第二高的人 -
查询除了 boss 工资最高的人。
在题1的基础上使用 where 排除掉 boss。
select `ename`,`salary` from `tb_employee` where `salary`=( select max(salary) from `tb_employee` where `position`<>'boss' ); -
查询月薪 top 3的人。
这时出现了和题1一样的情况。 limit ，可能会错过工资并列的第四人；所以这里用题1的方法③最合适。
select `ename`,`salary` from `tb_employee` t1 where( select count(*) from `tb_employee` t2 where `t2`.`salary`>`t1`.`salary` )<3 order by `salary` desc; -
查询所有员工的姓名和年薪。年薪计算公式：月薪*12 补贴。
题目很简单，但是**在计算时，我们必须注意数据是否 Not Null，如果出现 Null 数据结果也会变成 Null。**要用 ifnull() 或 coalesce() 限制函数。
-
查询各部门名称和员工人数。
部门名称在部门表中，每个部门的人数需要根据员工表中的部门号进行统计。显然，在连接结果表和部门表之前，有必要检查员工表中的员工号码和统计员工信息。**注意几种连接的差异。**比如这个问题，即使有些部门没有员工，也要显示出来，所以部门要表 left join 派生表。
select dname,total from `tb_department` t1 left join ( select dname, ifnull(count(*), as total from `tb_department` t2 group by `stu_id` ) on t1.`stu_id`=t2.`stu_id`; -
查询各部门平均工资高于本部门的员工名称和工资。记得说清楚属于哪个表格，否则会报错 ambigious
先按部门分组查询平均分数和员工号表；然后通过部门号和员工表连接，筛选出工资值大于平均值的。
select sname,salary from `tb_employee` t1 inner join ( select avg(salary),dno from `tb_employee` group by dno; )t2 on t1.dno=t2.dno and t1.salary > t2.avg(salary);-- 两个连表条件 -
查询每个部门工资最高的员工的用户姓名、工资和部门名称。
派生表通过工资值、部门号、员工表、部门表连接。
-
查询4-6名员工的工资、姓名、。
问题1:如何查出排名？
问题2:这个问题并不像预期的那么简单。比如员工工资前8名是5000、5000、4000、4000、3000、3000、3000、2000，其实第三、第四人并列第三，4-6显示的排名值应该是3、5、5
解决：mysql 8的窗口函数可以解决排名问题 / top N 问题。
①无窗口函数：
一般不写系统变量@或写两个@@（可以通过 show variables 查询)，写自己定义的变量@，赋值方法：
set @a=0;或select@a:=0;而且变量也可以通过 as 起别名。先定义一个变量，从0开始，每次选择+1，都可以作为序号使用。
set @a=0; select row_num,ename,salary from ( select @a=@a 1 as `row_num`,ename,salary,(select @a:=0) -- @a 重新赋值为0 from `tb_employee` order by salary desc ) where `row_num` between 4 and 6;-- 在子查询中做了几次查询，@a 就加几次注意不要用括号 limit 3 offset 3，因为这样的话子查询就只会进行3次，@a 值只有1到3.
另外，每次查询都要重新给予@a 赋值为0，否则其值会累积。
-
查询各部门薪酬排名前两的员工。
Top N 问题通过题1的做法③解决。
select eno,ename,salary,dno from `tb_employee` t1 where ( select count(*) from `tb_employee` t2 where t1.`dno`=t2.`dno` and t2.salary>t1.salary )<2 order by t1.dno asc, t1.salary desc;
窗口函数
内容来源：通俗易懂的学习：SQL 窗口函数 - 知乎
应用于组内排名和 Top N 类问题。 一般是处理 where 和 group by
窗口函数不仅仅是函数，还有一套完整的语法。
<窗口函数> over (partition by <列名用于分组> order by <列名用于排序>) <窗口函数> 放置聚合函数或特殊窗口函数。
以一列的形式使用窗口函数。
特殊窗口函数
rank、dese rank、row_number
partition by 和 group by 的区别
使用：
select `ename`,`sal`, rank() over (order by `sal` desc)as `r1`, dense_rank() over (order by `sal` desc)as `r2`, row_number() over (order by `sal` desc)as `r3` from `tb_emp`; 第八题窗口函数做法:加一个 where r between 4 and 6 的条件。
至于使用哪种特殊函数，则取决于要求。
第九题窗口函数做法:因为分组产生，不能直接使用 where r <=2.但窗口函数分组后的列作为筛选，不能直接使用 where 也不能用 having。窗口函数的查询结果应作为衍生表使用 where 做选择。
select `ename`,`sal`,`dno` from( select `ename`,`sal`,`dno`, rank() over (partition by `dno` order by `sal` desc)as `r` from `tb_emp` ) `temp` where `r`<=2;-- 不能在派生表里直接筛选 窗口函数性能相对较差，不应在业务中使用，数据分析师可能会经常使用。