3,491
社区成员
发帖
与我相关
我的任务
分享
SELECT A.*,ROW_NUMBER() OVER(PARTITION BY a1,a2,a3,a4 ORDER BY a5 DESC)RN
FROM A;
取RN=1的记录
create table T(
a1 number(5),
a2 number(5),
a3 number(5),
a4 number(5),
a5 number(5)
);
insert into T(a1,a2,a3,a4,a5)
values(101,1,2,3,4);
insert into T(a1,a2,a3,a4,a5)
values(102,2,3,4,6);
insert into T(a1,a2,a3,a4,a5)
values(102,2,3,4,51);
insert into T(a1,a2,a3,a4,a5)
values(103,3,4,5,70);
insert into T(a1,a2,a3,a4,a5)
values(103,3,4,5,7);
insert into T(a1,a2,a3,a4,a5)
values(103,3,4,5,12);
insert into T(a1,a2,a3,a4,a5)
values(104,4,5,6,71);
insert into T(a1,a2,a3,a4,a5)
values(104,4,5,6,98);
insert into T(a1,a2,a3,a4,a5)
values(104,4,5,6,101);
insert into T(a1,a2,a3,a4,a5)
values(104,4,5,6,11);
insert into T(a1,a2,a3,a4,a5)
values(105,5,6,7,78);
insert into T(a1,a2,a3,a4,a5)
values(105,5,6,7,74);
insert into T(a1,a2,a3,a4,a5)
values(105,5,6,7,75);
insert into T(a1,a2,a3,a4,a5)
values(105,5,6,7,55);
insert into T(a1,a2,a3,a4,a5)
values(105,5,6,7,4);
SQL> select * from T;
A1 A2 A3 A4 A5
------ ------ ------ ------ ------
101 1 2 3 4
102 2 3 4 6
102 2 3 4 51
103 3 4 5 70
103 3 4 5 7
103 3 4 5 12
104 4 5 6 71
104 4 5 6 98
104 4 5 6 101
104 4 5 6 11
105 5 6 7 78
105 5 6 7 74
105 5 6 7 75
105 5 6 7 55
105 5 6 7 4
--方法1:Aspen
SQL> select a1,a2,a3,a4,a5
2 from T ta1
3 where not exists(select 1 from T ta2
4 where ta1.a1=ta2.a1
5 and ta1.a2=ta2.a2
6 and ta1.a3=ta2.a3
7 and ta1.a4=ta2.a4
8 and ta1.a5<ta2.a5);
/*
A1 A2 A3 A4 A5
------ ------ ------ ------ ------
101 1 2 3 4
103 3 4 5 70
102 2 3 4 51
105 5 6 7 78
104 4 5 6 101
Executed in 0.016 seconds
*/
--方法2:子查询
SQL> select a1,a2,a3,a4,a5
2 from T
3 where (a1,a2,a3,a4,a5) in
4 (select a1,a2,a3,a4,max(a5)
5 from T
6 group by a1,a2,a3,a4);
/*
A1 A2 A3 A4 A5
------ ------ ------ ------ ------
101 1 2 3 4
102 2 3 4 51
103 3 4 5 70
104 4 5 6 101
105 5 6 7 78
Executed in 0.016 seconds
*/
--比较:
/*
总觉得子查询的效率要高点,这里或许是数据量小的缘故吧,这里执行时间都差不多
子查询中:先将a1,a2,a3,a4相同的行分为一组,在取出a5最大的那一行
而且始终在同一张表中进行的
*/
/*
方法1:将一张表看做两张,再将两张表连接起来,然后再将两表中a1,a2,a3,a4相同的数据行进行比较
取出a5最大的一行,如果数据量大的话,这种无休止的比较可不是一种好的选择;
方法2也将一张表当做两张,但没将两张表连接起来,子查询select明显将数据量减小了:只取a5最大的行。
*/
select a1,a2,a3,a4,a5
from a ta1
where not exists(select 1 from a ta2
where ta1.a1=ta2.a1
and ta1.a2=ta2.a2
and ta1.a3=ta2.a3
and ta1.a4=ta2.a4
and ta1.a5<ta2.a5)