sql - RANK 分区函数与 SUM OVER 配合使用

我有以下可行的方法，但我认为我已经完成了我通常的技巧，使一些可能更简单的事情变得过于复杂。

如果您运行该脚本，您将看到我想要实现的目标 - 最初只是按部门得分排名，然后按每个部门内每个姓名的得分排名。

如何简化以下内容？:

IF OBJECT_ID('TEMPDB..#Table') IS NOT NULL BEGIN DROP TABLE #Table END;
 CREATE TABLE #Table
    (
    Department VARCHAR(100),
    Name VARCHAR(100),
    Score INT
    );

INSERT INTO #Table VALUES
('Sales','michaeljackson',7),
('Sales','jim',10),
('Sales','jill',66),
('Sales','j',1),
('DataAnalysis','jagoda',66),
('DataAnalysis','phil',5),
('DataAnalysis','jesus',6),
('DataAnalysis','sam',79),
('DataAnalysis','michaeljackson',9999);

WITH SumCte AS
    (
    SELECT  Department,
        sm = sum(Score)
    FROM    #Table
    GROUP BY Department
    )
, RnkDepCte AS
    (
    SELECT  Department,
        rk =RANK() OVER (ORDER BY sm DESC)
    FROM    SumCte
    )
, RnkCte AS
    (
    SELECT  Department,
        Name,
        Score,
        rnk = RANK() OVER (PARTITION BY a.Department ORDER BY a.Score DESC)
    FROM    #Table a
    )
SELECT a.Department,
       a.Name,
       a.Score,
       FinalRank = RANK() OVER (ORDER BY ((10000/b.rk) + (100/a.rnk)) DESC)
FROM   RnkCte a
       INNER JOIN RnkDepCte b
        ON a.Department = b.Department

最佳答案

有一个更简单的方法。试试这个:

select t.*,
       RANK() over (order by sumscore desc, score desc)
from (select t.*,
             SUM(score) over (partition by department) as SumScore
       from #Table t
      ) t

关于sql - RANK 分区函数与 SUM OVER 配合使用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15007067/