Question

SQL Server 2005 出现问题:

假设我有一个表格 PLAYER ( string播放器, int 评分, bol active ) 和这个查询 :

SELECT PLAYER.player AS NAME,
   PLAYER.score AS SCORE,
   POSITION = CASE WHEN PLAYER.Active THEN RANK()OVER(ORDER BY score desc) else NULL end
from PLAYER

问题是当玩家不活动时, 所产生的位置不是连续的。

例如:

JOHN,10000,1
PETER,5000,NULL (NOT ACTIVE)
CHARLES,2500,3 (SHOULD HAVE POSITION 2, NOT 3)

我为我可怕的英语感到抱歉,我希望我已经解释了我的观点

Answer 1

declare @Player table
(
  player varchar(20),
  score int,
  state int
)

insert into @Player values ( JOHN , 10000, 1)
insert into @Player values ( PETER , 5000, NULL)
insert into @Player values ( PAUL , 5000, 2)
insert into @Player values ( CHARLES , 2500, 1)

select player as Name,
       score,
       case
          when state = 1 then
            rank() over(partition by state order by score desc)
          else null
       end as position
from @Player

结果:

Name                 score       position
-------------------- ----------- --------------------
PETER                5000        NULL
JOHN                 10000       1
CHARLES              2500        2
PAUL                 5000        NULL

Answer 2

尝试此 :

POSITION = 
   RANK()OVER(ORDER BY CASE WHEN Active IS NULL THEN 1 ELSE 0 END ASC, score DESC)

< 强度 > 编辑 :

...but the problem is that actually my "Active" column is not boolean, the real column is an integer called "state" and it must equal 1 to be active. How should the code be in that case?

然后,这应当奏效:

POSITION = 
   RANK()OVER(ORDER BY CASE WHEN State = 1 THEN 0 ELSE 1 END ASC, score DESC)

Answer 3

尝试此 :

SELECT PLAYER.player AS NAME, 
PLAYER.score AS SCORE, 
POSITION = RANK()OVER(ORDER BY score desc) 
from PLAYER
WHERE ACTIVE IS NOT NULL
UNION
SELECT PLAYER.player AS NAME, 
PLAYER.score AS SCORE,NULL  
from PLAYER
WHERE ACTIVE IS NULL

与已经回答的询问相比,我不知道这样做会有多有效率,但是这容易理解。

友情链接