tb1
name no
AAAA 2
BBBB 3
AAAA 3
查询结果
name no
AAAA 2
BBBB 3
select distinct name,no from tb1 不行
只判断name是否重复,如果有2个AAAA,后面no不一样,随便取哪个都可以,所以distinct不行,取出来还是3行
name no
AAAA 2
BBBB 3
AAAA 3
查询结果
name no
AAAA 2
BBBB 3
select distinct name,no from tb1 不行
只判断name是否重复,如果有2个AAAA,后面no不一样,随便取哪个都可以,所以distinct不行,取出来还是3行
select * from tb1 A where no=(select min(no) from tb1 where name=A.name)
select * from tb1 a where not exist (select 1 from tb1 b where a.[name]=b.[name] and a.no>b.no)
declare @tb1 table (name varchar(4),no varchar(2))
insert into @tb1
select 'AAAA','2a' union all
select 'BBBB','3b' union all
select 'AAAA','3c'select * from @tb1 t
where no=
(select min(no) from @tb1 where name=t.name )
order by name
/*
name no
---- ----
AAAA 2a
BBBB 3b
*/
--是不是数字没有关系
(select distinct [name] from tb1) a
cross apply
(select top(1) * from tb1 where [name] = a.[name] /*order by field_list*/) b
select name,max(no) from tb1 group by name 或
select name,min(no) from tb1 group by name
select 'AAAA','2'
union all select 'BBBB','3'
union all select 'AAAA','3'select name,[no] from (
select *,ROW_NUMBER() over (partition by name order by [no] desc) as row from #tb) as T
where T.row = 1name no
-------------------- -----------
AAAA 3
BBBB 3(2 行受影响)
insert into @tb1
select 'AAAA','2' union all
select 'BBBB','3' union all
select 'AAAA','3'select ROW_NUMBER()over(order by name) as rid,* into #a from @tb1 order by nameselect a.name,a.no from #a a left join #a b on a.rid=b.rid+1
where a.name<>isnull(b.name,'')