create table test(A varchar(10),B int)
insert test select '001',1
union all select '001',2
union all select '002',3
union all select '002',4
union all select '002',5select * from test where B in
(
select max(B) from test group by A
)
insert test select '001',1
union all select '001',2
union all select '002',3
union all select '002',4
union all select '002',5select * from test where B in
(
select max(B) from test group by A
)
a varchar(3),
b int
)
insert into test
select '001',1 union all
select '001',2 union all
select '002',3 union all
select '002',4 union all
select '002',5select a,(select top 1 b from test where t.a=test.a order by newid())b
from test t
--where a='001' or a='002'
group by a
a varchar(3),
b varchar(10)
)
insert into test
select '001','一' union all
select '001','二' union all
select '002','三' union all
select '002','四' union all
select '002','五'select a,(select top 1 b from test where t.a=test.a order by newid())b
from test t
--where a='001' or a='002'
group by adrop table test
insert test select '001','你好'
union all select '001','不好'
union all select '002','不好'
union all select '002','好好'
union all select '002','dfe'select id=identity(int,1,1),* into # from testselect A,B from # t where id =
(
select top 1 id from # where A=t.A
)
drop table test,#A B
---------- --------------------
001 你好
002 不好
select a,min(b) as b from tablename group by a