34,571
社区成员
发帖
与我相关
我的任务
分享
declare @tb1 table (name varchar(4),no varchar(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 name
select a.name,a.no from #a a left join #a b on a.rid=b.rid+1
where a.name<>isnull(b.name,'')
create table #tb (name nvarchar(20),[no] int)
insert into #tb
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 = 1
name no
-------------------- -----------
AAAA 3
BBBB 3
(2 行受影响)
select b.* from
(select distinct [name] from tb1) a
cross apply
(select top(1) * from tb1 where [name] = a.[name] /*order by field_list*/) b
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 * from tb1 a where not exist (select 1 from tb1 b where a.[name]=b.[name] and a.no>b.no)
select * from tb1 A where no=(select min(no) from tb1 where name=A.name)