SQL实现：仅对VisitCount≥Average_VisitCount的记录按[% Score]进行排名的问题

我明白你遇到的问题了——你当前的代码里，虽然用CASE把不符合VisitCount >= Average_VisitCount条件的行的排名设为了NULL，但RANK()函数的窗口计算仍然包含了分区内的所有行，那些不符合条件的行还是会干扰排名的结果，导致最终的排名不符合预期。

要解决这个问题，我们需要确保排名计算仅针对符合条件的记录，然后再将结果关联回原表，让不符合条件的行保持NULL即可。这里给你提供一个清晰的解决方案：

WITH RankedQualifiedRows AS (
    -- 先仅筛选符合条件的行，计算它们的排名
    SELECT 
        CommID,
        ProvID,
        TimePeriod,
        -- 按[% Score]降序排名，得分最高的排第1
        RANK() OVER (PARTITION BY CommID, TimePeriod ORDER BY [% Score] DESC) AS Highest,
        -- 按[% Score]升序排名，得分最低的排第1
        RANK() OVER (PARTITION BY CommID, TimePeriod ORDER BY [% Score]) AS Lowest
    FROM #df
    WHERE VisitCount >= Average_VisitCount
)
-- 将排名结果左连接回原表，保留所有行
SELECT 
    a.CommID,
    a.ProvID,
    a.VisitCount,
    a.[% Score],
    a.TimePeriod,
    a.Median_VisitCount,
    a.Average_VisitCount,
    rqr.Highest,
    rqr.Lowest
FROM #df a
LEFT JOIN RankedQualifiedRows rqr
    ON a.CommID = rqr.CommID
    AND a.ProvID = rqr.ProvID
    AND a.TimePeriod = rqr.TimePeriod
ORDER BY a.CommID, a.TimePeriod, a.VisitCount DESC;

为什么这个方法有效？ #

1. CTE筛选精准：RankedQualifiedRows这个公共表表达式里，我们只保留VisitCount >= Average_VisitCount的行，所有排名计算都基于这个子集，不会被不符合条件的行干扰。
2. 左连接保留全量数据：通过左连接原表，我们既能拿到符合条件行的正确排名，也能让不符合条件的行的Highest和Lowest字段自然显示为NULL，完全符合你的需求。

对比你原来的代码，这个方案从根源上确保了排名的计算范围是你想要的，不会出现“看似过滤了但实际参与排名”的问题。

备注：内容来源于stack exchange，提问作者Trevor M