有下面这样的一组数据在表TEST中
字段名: A B C D
表数据: a a a 1000
a a b 2000
a a c 3000
要查询得到A、B字段相同,D字段值为最大的C字段值。
结果如下:
a a c 3000本人菜鸟,只做下面这一步。请各位大侠专家帮忙!
select distinct a, b,max(d)
from test t
group by a, b;
字段名: A B C D
表数据: a a a 1000
a a b 2000
a a c 3000
要查询得到A、B字段相同,D字段值为最大的C字段值。
结果如下:
a a c 3000本人菜鸟,只做下面这一步。请各位大侠专家帮忙!
select distinct a, b,max(d)
from test t
group by a, b;
from test a) where rn=1
select a,b,c,d from (select a.*,row_number()over(partition by a,b order by d desc) as rn
from test a) where rn=1
from a,(select a,b,max(d) d from a group by a,c)t
where a.a=t.a and a.b=t.b and a.d=t.d
不排序也不用行号,这个行不行?
SQL> create table test(
2 A varchar2(10),
3 B varchar2(10),
4 C varchar2(10),
5 D number(10)
6 );Table created.SQL> insert into test values('a','a','ttttt',1000);1 row created.SQL> insert into test values('b','a','jjjjj',1500);1 row created.SQL> insert into test values('d','d','kkkkk',1600);1 row created.SQL> insert into test values('a','a','pppppp',1700);1 row created.SQL> select * from test;A B C D
---------- ---------- ---------- ----------
a a ttttt 1000
b a jjjjj 1500
d d kkkkk 1600
a a pppppp 1700SQL> commit;Commit complete.SQL> select * from test where (A,B,D) in
2 (
3 select A,B,max(D) from
4 ( select * from test where A=B ) X
5 group by A,B
6 );A B C D
---------- ---------- ---------- ----------
d d kkkkk 1600
a a pppppp 1700
SQL> select a,b,c,d from (select a.*,row_number()over(partition by a,b order by d desc) as rn
2 from test a) where rn=1;A B C D
---------- ---------- ---------- ----------
a a pppppp 1700
b a jjjjj 1500
d d kkkkk 1600