SQL Server中如何引用COUNT()结果进行RANK排序？

你遇到的错误是因为SQL Server的逻辑执行顺序限制：同一SELECT语句中，列别名不能被窗口函数直接引用（窗口函数的计算逻辑在列别名解析之前）。以下是两种可行的解决方法：

方法1：直接在窗口函数中使用COUNT(*) #

既然IdCount本质就是COUNT(*)的结果，直接把COUNT(*)放到窗口函数的ORDER BY子句里即可：

SELECT 
    [id],
    COUNT(*) AS IdCount,
    RANK() OVER ( 
        ORDER BY COUNT(*)
    ) CountRank
FROM  myTable
WHERE DATEDIFF(day,[Time],GETDATE()) < 30
GROUP BY [id]

方法2：使用子查询/CTE先计算统计值 #

如果统计逻辑复杂，或者需要复用这个统计结果，可以先通过子查询或CTE算出IdCount，再在外层查询中进行排名：

子查询版本 #

SELECT 
    [id],
    IdCount,
    RANK() OVER ( 
        ORDER BY IdCount
    ) CountRank
FROM (
    SELECT 
        [id],
        COUNT(*) AS IdCount
    FROM  myTable
    WHERE DATEDIFF(day,[Time],GETDATE()) < 30
    GROUP BY [id]
) AS subQuery

CTE版本 #

WITH IdStats AS (
    SELECT 
        [id],
        COUNT(*) AS IdCount
    FROM  myTable
    WHERE DATEDIFF(day,[Time],GETDATE()) < 30
    GROUP BY [id]
)
SELECT 
    [id],
    IdCount,
    RANK() OVER ( 
        ORDER BY IdCount
    ) CountRank
FROM IdStats

这两种方法都能避开别名引用的限制，顺利完成排名计算。

内容的提问来源于stack exchange，提问作者n179911a