MySQL 一表分组查询max
2018-06-20 本文已影响1人
Kaidi_G
一个很常见的较为复杂的查询,在同一张表里求某个分组下某个属性最高的前N位记录,主要有两种方法实现,这里详细说明一下。
假设一张表记录了员工,薪水和部门Id
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
另一张表记录了部门ID和名字
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
我们希望求每个部门里薪水最高的员工和信息。
方法一 实用,简单,适用于只求每组最高/最低记录的情况
思路为,用employee表A与自己(B)进行联合查询,
联合条件为A.departmentid = B.departmentid,意即将同一部门的人进行全对比,
选取所有对比中的MAX(salary),就自然会得到同一部门里面最高salary的记录
这样就可以不用group by 的取得分组里的最大记录了
select * from employee a
where a.salary = (
select max(salary) from employee b
where b.departmentId = a.departmentId
)
这个时候我们就可以在外层再联合department表将部门id替换为部门名字
select d.name as department, t.name as employee, t.salary
from department d,(
select * from employee a
where a.salary = (
select max(b.salary)
from employee b
where b.departmentId = a.departmentId
)
)t
where d.id = t.departmentid
不过这个嵌套结构也可以稍微进行改进,就是我们先把部门表和其中一个employee表交了,得到带部门名称的一张表,再与原始employee联合查询
select d.name as Department, a.name as Employee, a.Salary
from employee a
join department d
on d.id = a.departmentid
where a.salary = (
select max(b.salary)
from employee b
where b.departmentId=a.departmentId)
方法二 暴力统计,可以计算每组前N位的记录,但不能只输出最高记录,意即N必须大于1
把employee表和自己左交,联合条件依然是a.departmentId = b.departmentId,以及,a.salary < b.salary.
这时会产生一张大表,记录了所有同部门之间的比较,
也就是说,对于某个部门最高薪水的那个人,他的记录会出现1次(因为左交一定会显示左表全部行哪怕没有匹配),表右部分为NULL,
而薪水最低的那个人,他的记录会出现n-1次,意即,在部门里其他人都比他工资高。
得到这张表之后,只需要按a的部门分组统计count有几条记录。
最高薪水的人应该只有一条记录,
第二高的人count也只有一条记录(只有第一名比他薪水高),
第三名count应该有两条记录(第一名和第二名)....
但是注意,此方法无法区分第一名和第二名(都是一条记录),所以无法只选出第一名(但是可以根据salary的具体数值在输出表进行排序)
select * from employee a
join employee b
on a.departmentId = b.departmentId
and a.salary < b.salary
group by a.Id,a.departmentId
having count(*)<3