或者 SELECT * FROM A T WHERE NOT EXISTS(SELECT * FROM A WHERE B=T.B AND C=T.C AND D>T.D)
select a.B,a.C,b.D from (select B,C from test group by B,C) a, (select B,C,D from test order by B,C,D desc) b where a.B=b.B and a.C=b.C
select b , c , max(D) d from A group by b , c
create table tb(B varchar(10), C varchar(10), D int) insert into tb values('a' , 'b' , 300) insert into tb values('a' , 'b' , 200) insert into tb values('b' , 'c' , 100) insert into tb values('b' , 'c' , 200) goselect b , c , max(d) d from tb group by b , cdrop table tb /* b c d ---------- ---------- ----------- a b 300 b c 200(所影响的行数为 2 行) */
SELECT *
FROM A T
WHERE NOT EXISTS(SELECT * FROM A WHERE B=T.B AND C=T.C AND D>T.D)
from
(select B,C from test group by B,C) a,
(select B,C,D from test order by B,C,D desc) b
where a.B=b.B and a.C=b.C
insert into tb values('a' , 'b' , 300)
insert into tb values('a' , 'b' , 200)
insert into tb values('b' , 'c' , 100)
insert into tb values('b' , 'c' , 200)
goselect b , c , max(d) d from tb group by b , cdrop table tb /*
b c d
---------- ---------- -----------
a b 300
b c 200(所影响的行数为 2 行)
*/