数分必会mysql 日常取数100例(一)
大家好,我是数据小郑。
本篇是数分必会mysql系列的第四篇:日常取数100例(一)。
系列内容,请看🚩话题,订阅后文章更新可第一时间推送至订阅号。
mysql的基础点在1、2、3已经总结过了,接下来准备更新日常取数100例,在实际业务场景中熟练取数技能。案例均取自热点面试题和真实工作场景。
文章分为3部分,第一部分为业务取数分析,第二部分为代码取数和结果展示,第三部分为代码逻辑简述。
获取每个小组中当前员工薪水最高的相关信息,给出的dpet_no,emp_no以及对应的salary,按照小组的编号升序排列。
员工表dept_no的注意点是to_date的选择,注意剔除离职员工;salary表注意选择仍在发薪的人员记录。
员工表dept_no如下:
薪水表如下:
解法1:
select d.dept_no,d.emp_no,max(s.salary) from dept_emp as d
left join salary s on d.emp_no=s.emp_no
group by d.dept_no;
解法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_no
order by s.salary desc) m from dept_emp as d,
salary as s where d.emp_no=s.emp_no
and d.to_date='9999-01-01') as a where m=1;
解法3:
select d.dept_no,d.emp_no,s.salary from
dept_emp d,salary s where d.emp_no=s.emp_no
and s.salary=(select max(s2.salary) from dept_emp d2,
salary s2 where d2.emp_no=s2.emp_no
and d2.dept_no=d.dept_no group by d2.dept_no) order by dept_no;
首先我们说三种解法中,解法一是很多人会写的一种解法,且结果与另外2种方法的结果不是一致的:员工号出错了。在这里我们想强调一下,max(salary)与emp_no不一定是一一对应的,group by默认取非聚合的第一条记录,也就是说员工号是随机取的第一条数据,最大薪水值并不是对应相应的员工。
然后我们看第二种解法,使用了窗口排序函数。我们分步查看。运行以下代码得到每个部门/小组的内部排序。然后我们选择排名为第一的那条数据就是我们需要的数据了。
select d.dept_no,d.emp_no,s.salary,rank()over(partition by d.dept_no
order by s.salary desc) m from dept_emp as d,
salary as s where d.emp_no=s.emp_no
and d.to_date='9999-01-01';
最后我们看下第三种解法,这种解法利用了group by只有可以取到每个部门/小组的最大薪水值的特点,通过嵌套避免了解法1随机第一条员工值的问题,dept_emp 表用了2次,解法注意d2.dept_no=d.dept_no的条件即可。
总结:
1.在可以使用窗口排序函数的情况下,我更喜欢选择解法2,逻辑更加清晰,简单;
2.max(salary)和emp_no不一定一一对应,group by 默认取非聚合的第一条记录!
原创不易,欢迎点赞、留言、分享,支持我继续写下去
我是数据小郑,最近正在原创系列话题,欢迎订阅。订阅后,文章更新第一时间推送至订阅号,每篇文章不错过。