日期:2014-05-18 浏览次数:20455 次
create table TABLEA(ID int, VALUE varchar(10)) insert into TABLEA values(1 ,'A') insert into TABLEA values(1 ,'B') insert into TABLEA values(1 ,'C') insert into TABLEA values(1 ,'D') insert into TABLEA values(2 ,'C') insert into TABLEA values(2 ,'D') insert into TABLEA values(2 ,'E') insert into TABLEA values(2 ,'F') go --如果只有id,value这两列,则可以直接分组取最大。 select max(id) id , value from TABLEA WHERE ID IN (1,2) group by value /* id value ----------- ---------- 1 A 1 B 2 C 2 D 2 E 2 F (所影响的行数为 6 行) */ --如果不止id,value这两列,则如下:、 select t.* from TABLEA t WHERE ID IN (1,2) and id = (select max(id) from tablea where value = t.value and id in (1,2)) order by t.value /* id value ----------- ---------- 1 A 1 B 2 C 2 D 2 E 2 F (所影响的行数为 6 行) */ select t.* from TABLEA t WHERE ID IN (1,2) and not exists (select 1 from tablea where value = t.value and id in (1,2) and id > t.id) order by t.value /* id value ----------- ---------- 1 A 1 B 2 C 2 D 2 E 2 F (所影响的行数为 6 行) */ drop table tablea