select * from table where cast(a as char(2))+b in (select cast(a as char(2))+b from (select a=max(a), b from table group b))
select * from table where cast(a as char(20))+b in (select cast(a as char(20))+b from (select a=max(a), b from table group b))
declare @tab table(a varchar(10),b varchar(10),c varchar(10)) insert into @tab select '1','www','aa' union select '2','www','bb' union select '3','ddd','dd' union select '4','eee','ee' union select '5','www','cc' union select '6','eee','ee'
select * from @tab select * from @tab a where a in (select top 1 a from @tab where b=a.b) 结果: (所影响的行数为 6 行)a b c ---------- ---------- ---------- 1 www aa 2 www bb 3 ddd dd 4 eee ee 5 www cc 6 eee ee(所影响的行数为 6 行)a b c ---------- ---------- ---------- 1 www aa 3 ddd dd 4 eee ee(所影响的行数为 3 行)
select * from table a where not exists(select 1 from table where a.b=b and a.a>a)
1 www ....
2 www ....
3 ddd ....
4 eee ....最后形成这样一个形式
1 www ....
3 ddd ....
4 eee ....
union
select '2','www','bb'
union
select '3','ddd','dd'
union
select '4','eee','ee'
union
select '5','www','cc'
union
select '6','eee','ee'
select * from @tab select * from @tab a where a in (select top 1 a from @tab where b=a.b)
结果:
(所影响的行数为 6 行)a b c
---------- ---------- ----------
1 www aa
2 www bb
3 ddd dd
4 eee ee
5 www cc
6 eee ee(所影响的行数为 6 行)a b c
---------- ---------- ----------
1 www aa
3 ddd dd
4 eee ee(所影响的行数为 3 行)
where not exists(select 1 from table where a.b=b and a.a>a)