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)
select a1,a2,a3,a4,max(a5) a5 from tb group by a1,a2,a3,a4
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 78Executed in 0.016 seconds */--比较: /* 总觉得子查询的效率要高点,这里或许是数据量小的缘故吧,这里执行时间都差不多 子查询中:先将a1,a2,a3,a4相同的行分为一组,在取出a5最大的那一行 而且始终在同一张表中进行的 */ /* 方法1:将一张表看做两张,再将两张表连接起来,然后再将两表中a1,a2,a3,a4相同的数据行进行比较 取出a5最大的一行,如果数据量大的话,这种无休止的比较可不是一种好的选择; 方法2也将一张表当做两张,但没将两张表连接起来,子查询select明显将数据量减小了:只取a5最大的行。 */
SELECT A.*,ROW_NUMBER() OVER(PARTITION BY a1,a2,a3,a4 ORDER BY a5 DESC)RN FROM A;取RN=1的记录
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)
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 78Executed in 0.016 seconds
*/--比较:
/*
总觉得子查询的效率要高点,这里或许是数据量小的缘故吧,这里执行时间都差不多
子查询中:先将a1,a2,a3,a4相同的行分为一组,在取出a5最大的那一行
而且始终在同一张表中进行的
*/
/*
方法1:将一张表看做两张,再将两张表连接起来,然后再将两表中a1,a2,a3,a4相同的数据行进行比较
取出a5最大的一行,如果数据量大的话,这种无休止的比较可不是一种好的选择;
方法2也将一张表当做两张,但没将两张表连接起来,子查询select明显将数据量减小了:只取a5最大的行。
*/
SELECT A.*,ROW_NUMBER() OVER(PARTITION BY a1,a2,a3,a4 ORDER BY a5 DESC)RN
FROM A;取RN=1的记录