需求,先分组,并且查询非当前分组字段的某个字段的最大值,对应的一行数据进行展示。
准备工作:
create table tb_group_test (
id varchar2(64) primary key,
name varchar2(50),
sex varchar2(2),
age varchar2(3),
score varchar2(5)
);
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('1', 'aa', '1', '22', '34');
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('2', 'aa', '1', '22', '45');
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('3', 'bb', '2', '33', '');
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('4', 'bb', '2', '33', '32');
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('5', 'bb', '2', '33', '34');
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('6', 'bb', '2', '33', '56');
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('7', 'bb', '2', '33', '74');
insert into tb_group_test (ID, NAME, SEX, AGE, SCORE)
values ('7', 'bb', '2', '33', '74');
commit;
原来使用group by实现,需要筛选最大值的那一列必须唯一。
SELECT a.* from tb_group_test a where (a.name,a.sex,a.age) in
(select name,sex,age from tb_group_test group by name,sex,age )
and id in (select min(id) from tb_group_test group by name,sex,age);
结果:
使用row_number() over(partition by)函数 可以对分组列以外的任意列
说明:
开窗函数,Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。
用法:
row_number() over (partition by 分组字段 order by 任意字段)
select * from (
select t.*,row_number() over (partition by name,sex,age order by score desc nulls last) rn from tb_group_test t
) where rn =1;
结果:
注:空值默认排序是最大的。所以用了 order by 字段 nulls last ;也可以手动设置最顶 order by 字段 nulls first。