Mysql_8 SQL 语句——DQL 例题及一些注意事项

1. 员工表包括员工号码、姓名、工资、职位、补贴 、部门等信息。查询薪酬最高的员工姓名和薪酬价值。

   做法①：最常见的子查询，先查出最大工资值，再筛选出员工表中的工资值等于这个数字的员工。

   做法②：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，工资第二高的人 

2. 查询除了 boss 工资最高的人。

   在题1的基础上使用 where 排除掉 boss。

   select `ename`,`salary` from `tb_employee`  where `salary`=(     select max(salary) from `tb_employee` where `position`<>'boss' ); 

3. 查询月薪 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; 

4. 查询所有员工的姓名和年薪。年薪计算公式：月薪*12 补贴。

   题目很简单，但是**在计算时，我们必须注意数据是否 Not Null，如果出现 Null 数据结果也会变成 Null。**要用 ifnull() 或 coalesce() 限制函数。

5. 查询各部门名称和员工人数。

   部门名称在部门表中，每个部门的人数需要根据员工表中的部门号进行统计。显然，在连接结果表和部门表之前，有必要检查员工表中的员工号码和统计员工信息。**注意几种连接的差异。**比如这个问题，即使有些部门没有员工，也要显示出来，所以部门要表 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`; 

6. 查询各部门平均工资高于本部门的员工名称和工资。记得说清楚属于哪个表格，否则会报错 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);-- 两个连表条件 

7. 查询每个部门工资最高的员工的用户姓名、工资和部门名称。

   派生表通过工资值、部门号、员工表、部门表连接。

8. 查询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，否则其值会累积。

9. 查询各部门薪酬排名前两的员工。

   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

使用：

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;-- 不能在派生表里直接筛选 

窗口函数性能相对较差，不应在业务中使用，数据分析师可能会经常使用。