表字段
ID TEAM SCORE,SCORE2,SCORE3
1 A 100 100 100
2 A 90 100 100
3 B 50 40 30
4 B 50 40 30
5 A 90 100 80
6 B 55 50 50
7 A 90 100 50
想按TEAM分组SCORE排名
如果SCORE相同，则按SCORE2，如果也相同，则按SCORE3，如果还相同，则按ID
不能要重复名次出现，即不能有两个第五名，然后第七名的情况。
即想得到的结果
ID TEAM SCORE,SCORE2,SCORE3 PM
1 A 100 100 100 1
2 A 90 100 100 2
5 A 90 100 50 3
7 A 90 100 50 4
6 B 55 50 50 1
3 B 50 40 30 2
4 B 50 40 30 3
先谢谢各位。
------解决方案--------------------
--把上面最后个条件id > a.id 改为 a < a.id
create table tb(ID int,TEAM varchar(10),SCORE int,SCORE2 int,SCORE3 int)
insert into tb values(1, 'A', 100 , 100 , 100)
insert into tb values(2, 'A', 90 , 100 , 100)
insert into tb values(3, 'B', 50 , 40 , 30 )
insert into tb values(4, 'B', 50 , 40 , 30)
insert into tb values(5, 'A', 90 , 100 , 80)
insert into tb values(6, 'B', 55 , 50 , 50)
insert into tb values(7, 'A', 90 , 100 , 50)
go
select * , PM=(select count(1) from tb where team=a.team and (score >a.score or (score = a.score and score2 >a.score2) or (score = a.score and score2=a.score2 and score3 >a.score3) or (score = a.score and score2=a.score2 and score3=a.score3 and id <a.id)))+1 from tb a order by team , pm
drop table tb
/*
ID TEAM SCORE SCORE2 SCORE3 PM
----------- ---------- ----------- ----------- ----------- -----------
1 A 100 100 100 1
2 A 90 100 100 2
5 A 90 100 80 3
7 A 90 100 50 4
6 B 55 50 50 1
3 B 50 40 30 2
4 B 50 40 30 3
（所影响的行数为 7 行）
(/
------解决方案--------------------
create table #tt(ID int,TEAM varchar(10),SCORE int,SCORE2 int,SCORE3 int)
ID TEAM SCORE,SCORE2,SCORE3 insert #tt select
1,'A',100,100,100 insert #tt select
2,'A',90,100,100 insert #tt select
3,'B',50,40, 30 insert #tt select
4,'B',50,40, 30 insert #tt select
5,'A',90,100,80 insert #tt select
6,'B',55,50, 50 insert #tt select
7,'A',90,100,50
--------------------借助临时表
select *,null PM into #temp from #tt order by team,SCORE desc,SCORE2 desc,SCORE3 desc,id
declare @a int,@b varchar(10)
select @a=1,@b=''
update #temp set [email protected],@a=(case when @b<>team then 1 else @a+1 end),@b=team
select * from #temp
--------------------
1 A 100 100 100 1
2 A 90 100 100 2
5 A 90 100 80 3
7 A 90 100 50 4
6 B 55 50 50 1
3 B 50 40 30 2
4 B 50 40 30 3
------解决方案--------------------
create table tb(ID int,TEAM varchar(10),SCORE int,SCORE2 int,SCORE3 int)
insert into tb values(1, 'A ', 100 , 100 , 100)
insert into tb values(2, 'A ', 90 , 100 , 100)
insert into tb values(3, 'B ', 50 , 40 , 30 )
insert into tb values(4, 'B ', 50 , 40 , 30)
insert into tb values(5, 'A ', 90 , 100 , 80)
insert into tb values(6, 'B ', 55 , 50 , 50)
insert into tb values(7, 'A ', 90 , 100 , 50)
go
select
*,row_number() over(partition by team order by team,score desc ,score2 desc ,score3 desc)