sql - 如何计算 SQL Server 中的唯一排名(没有任何重复)？

Question

我想计算唯一排名，但我得到重复排名

这是我的尝试:

SELECT 
    TG.EMPCODE, 
    DENSE_RANK() OVER (ORDER BY TS.COUNT_DEL DESC, TG.COUNT_TG DESC) AS YOUR_RANK
FROM
    (SELECT 
         EmpCode, 
         SUM(CASE WHEN Tgenerate = 1 THEN 1 ELSE 0 END) AS COUNT_TG 
     FROM 
         TBLTGENERATE1 
     GROUP BY 
         EMPCODE) TG
INNER JOIN  
    (SELECT 
         EMP_CODE, 
         SUM(CASE WHEN STATUS = 'DELIVERED' THEN 1 ELSE 0 END) AS COUNT_DEL 
     FROM 
         TBLSTAT 
     GROUP BY 
         EMP_CODE) TS ON TG.EMPCODE = TS.EMP_CODE;        

我得到的输出是这样的:

EID Rank
---------
102 1
105 2
101 2
103 3
106 4

105 和 101 的排名相同。

如何计算唯一排名？

Answer 1

使用 ROW_NUMBER() 代替 DENSE_RANK():

SELECT TG.EMPCODE, 
       ROW_NUMBER() OVER (ORDER BY TS.COUNT_DEL DESC, TG.COUNT_TG DESC) AS YOUR_RANK

然后将给平局按顺序排名。