vlambda博客
学习文章列表

数分必会mysql 日常取数100例(一)

数据小郑
竭力寻找方法工具优化日常工作流程,有更多时间思考快速稳健前行。
18篇原创内容
Official Account

大家好,我是数据小郑。


本篇是数分必会mysql系列的第四篇:日常取数100例(一)。


系列内容,请看🚩话题,订阅后文章更新可第一时间推送至订阅号。




mysql的基础点在1、2、3已经总结过了,接下来准备更新日常取数100例,在实际业务场景中熟练取数技能。案例均取自热点面试题和真实工作场景。


文章分为3部分,第一部分为业务取数分析,第二部分为代码取数和结果展示,第三部分为代码逻辑简述。


数分必会mysql 日常取数100例(一)
业务取数需求

获取每个小组中当前员工薪水最高的相关信息,给出的dpet_no,emp_no以及对应的salary,按照小组的编号升序排列。

员工表dept_no的注意点是to_date的选择,注意剔除离职员工;salary表注意选择仍在发薪的人员记录。


员工表dept_no如下:


数分必会mysql 日常取数100例(一)


薪水表如下:


数分必会mysql 日常取数100例(一)


数分必会mysql 日常取数100例(一)
代码取数

解法1:

select d.dept_no,d.emp_no,max(s.salary) from dept_emp as dleft join salary s on d.emp_no=s.emp_nogroup by d.dept_no;

数分必会mysql 日常取数100例(一)

解法2:

select a.dept_no,a.emp_no,a.salary from (select d.dept_no,d.emp_no,s.salary,rank()over(partition by d.dept_noorder by s.salary desc) m from dept_emp as d,salary as s where d.emp_no=s.emp_noand d.to_date='9999-01-01') as a where m=1;

数分必会mysql 日常取数100例(一)

解法3:

select d.dept_no,d.emp_no,s.salary from dept_emp d,salary s where d.emp_no=s.emp_noand s.salary=(select max(s2.salary) from dept_emp d2,salary s2 where d2.emp_no=s2.emp_noand d2.dept_no=d.dept_no group by d2.dept_no) order by dept_no;

数分必会mysql 日常取数100例(一)


数分必会mysql 日常取数100例(一)
逻辑简述

首先我们说三种解法中,解法一是很多人会写的一种解法,且结果与另外2种方法的结果不是一致的:员工号出错了。在这里我们想强调一下,max(salary)与emp_no不一定是一一对应的,group by默认取非聚合的第一条记录,也就是说员工号是随机取的第一条数据,最大薪水值并不是对应相应的员工。


然后我们看第二种解法,使用了窗口排序函数。我们分步查看。运行以下代码得到每个部门/小组的内部排序。然后我们选择排名为第一的那条数据就是我们需要的数据了。

select d.dept_no,d.emp_no,s.salary,rank()over(partition by d.dept_noorder by s.salary desc) m from dept_emp as d,salary as s where d.emp_no=s.emp_noand d.to_date='9999-01-01';


数分必会mysql 日常取数100例(一)


最后我们看下第三种解法,这种解法利用了group by只有可以取到每个部门/小组的最大薪水值的特点,通过嵌套避免了解法1随机第一条员工值的问题,dept_emp 表用了2次,解法注意d2.dept_no=d.dept_no的条件即可。


总结:

1.在可以使用窗口排序函数的情况下,我更喜欢选择解法2,逻辑更加清晰,简单;

2.max(salary)和emp_no不一定一一对应,group by 默认取非聚合的第一条记录!



原创不易,欢迎点赞、留言、分享,支持我继续写下去


我是数据小郑,最近正在原创系列话题,欢迎订阅。订阅后,文章更新第一时间推送至订阅号,每篇文章不错过。

数据小郑
竭力寻找方法工具优化日常工作流程,有更多时间思考快速稳健前行。
18篇原创内容
Official Account