实际业务中经常会对数据进行分组,有时会面临分组后一个组内有多条记录,但是只要一条记录(一般是时间最近的记录或某字段取值最大或最小的记录)。本文记录下可以使用的方法。
实例
实际中,有这样一个需求:从系统中导出了公司员工的个人信息,其中包含英语成绩,但是有的员工在填写时既填写了四级成绩又填了六级成绩,现在是想要每个员工的英语最高等级信息。
create table grades(employee_no int not null,
grade_level varchar(20),
level_num int
)
insert into grades values (000001,'大学英语四级',04),
(000002,'大学英语六级',06),
(000003,'大学英语四级',04),
(000003,'大学英语六级',06),
(000004,'大学英语六级',06)
方法一
第一个方法是生成一列分组排序,分组后对每组按照关心字段进行排序,那么只要取出排名为1(或n)的记录即可。这种方法的优势是:可以取出排名前n的记录(不只是第一条记录),劣势是:对排名的结果筛选需要再次使用一个子查询。
select employee_no,grade_level
from
(select *,row_number() over (partition by employee_no order by level_num desc) as rk
from grades
)t
where rk=1
方法二
如果只是取出第一条记录,有种方法是:原表和分组取出最大值或最小值进行左连接。但是这种方法只能取出数值字段或日期字段的最大值和最小值,char_length函数也只能计算字符串类型字段的长度。
select t1.employee_no,grade_level
from grades t1
left join (select employee_no,max(level_num) as max_level from grades group by employee_no) t2
on t1.employee_no=t2.employee_no
where t1.level_num=t2.max_level
方法三
第三种方法是:先对数据进行排序再进行分组,也能取出每组第一条记录。这种方法的思路是分组后只能取出每组以及每组的一个聚合结果,select 分组字段,其他字段最终返回分组字段及对应的第一条记录。但是这种方法不太鼓励。
select employee_no,grade_level
from (select * from grades order by level_num desc)t
group by employee_no