最近在写SQL时候遇到了一些苦恼的问题,在使用MySQL5版本时候想对字段进行排序,可是5版本的MySQL不支持自动排序,自己也是在网上查了好多方法,得出的结论是5版本的MySQL得自己定义参数进行排序,看的眼麻,心想还不如python或者Java直接排序得了。然后又看了MySQL8版本,好在8版本有开创函数,可以很简便的实现分组排序。其次,MySQL5版本中group by之后的字段可以去select后的部分,而MySQL8版本中group by之后的字段必须包括select之后的全部字段,这个就很烦,有时候我只想分一个组就好了,可MySQL8版本必须全部包含。
总的来说,想分组简便可以用MySQL5版本,想排序简便可以用MySQL8版本。
实例:
分组后取TopN
-
需求:获取每一个部门薪水最高的2名员工信息
-
分析:按照部门分组,每一部门的员工放在一组,然后基于这个组中的数据,按照工资降序排列。然后再根据排序后的结果集,获取排序为1的数据行即为结果
-
实现SQL: 如下:
-
select dept_no,emp_name,emp_salary,row_num from ( select case when @dept_no_g != x.dept_no then @row_num_g :=1 else @row_num_g :=@row_num_g + 1 end as row_num, x.emp_name, @dept_no_g := x.dept_no as dept_no, x.emp_salary, x.emp_hire_date from emp as x,(select @dept_no_g :='') as t1,(select @row_num_g :=0) as t2 order by dept_no,emp_salary desc ) res where row_num < 3
-
窗口函数实现:如下
-
select id,emp_name,dept_no,emp_salary,emp_hire_date,row_num from ( SELECT id,emp_name,dept_no,emp_salary,emp_hire_date,row_number() OVER w AS row_num FROM emp WINDOW w AS (PARTITION BY dept_no ORDER BY emp_salary DESC) order by dept_no ) as res where row_num <3