34,587
社区成员
发帖
与我相关
我的任务
分享
create table #ttt(XM nvarchar(50), BH int , GH varchar(50))
insert into #ttt values(N'李三',1,'A')
insert into #ttt values(N'李三',2,'A')
insert into #ttt values(N'李三',3,'A')
insert into #ttt values(N'张四',1,'A')
insert into #ttt values(N'张四',2,'A')
insert into #ttt values(N'王五',1,'B')
select xm,(select count(*)+1 from #ttt where bh>a.bh) as bhpm,bh from #ttt a order by bh desc
create table #ttt(XM nvarchar(50), BH int , GH varchar(50))
insert into #ttt values(N'李三',1,'A')
insert into #ttt values(N'李三',2,'A')
insert into #ttt values(N'李三',3,'A')
insert into #ttt values(N'张四',1,'A')
insert into #ttt values(N'张四',2,'A')
insert into #ttt values(N'王五',1,'B')
select * from(
select *, ROW_NUMBER() over(partition by XM order by BH desc ) rw from #ttt
where GH='A')t
where t.rw=1
结果
张四 2 A 1
李三 3 A 1
create table #ttt(XM nvarchar(50), BH int , GH varchar(50))
insert into #ttt values(N'李三',1,'A')
insert into #ttt values(N'李三',2,'A')
insert into #ttt values(N'李三',3,'A')
insert into #ttt values(N'张四',1,'A')
insert into #ttt values(N'张四',2,'A')
insert into #ttt values(N'王五',1,'B')
select * from(
select *, ROW_NUMBER() over(partition by XM order by GH desc ) rw from #ttt
where GH='A')t
where t.rw=1