如何让含PARTITION BY的OVER子句忽略主查询的WHERE条件

核心思路是先基于全表计算每个Name的首个周期标记，再最后应用WHERE过滤，避免提前过滤导致分区逻辑只作用于子集。

1. 创建测试用临时表和数据 #

CREATE TABLE #temp_table (
    Id INT,
    Cycle INT,
    DateStamp DATETIME,
    Name VARCHAR(50)
);

INSERT INTO #temp_table VALUES
(13, 1, '2024-01-01', 'Tom'),
(14, 2, '2024-01-02', 'Tom'),
(15, 1, '2024-01-01', 'Jerry'),
(16, 2, '2024-01-03', 'Jerry'),
(17, 1, '2024-01-02', 'Mike');

2. 正确的查询写法 #

用CTE先对全表做分区排序标记，再在外层执行过滤：

WITH FullTableRank AS (
    SELECT 
        *,
        -- 按Name分组，DateStamp、Cycle升序取首条标记为1
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY Name ORDER BY DateStamp, Cycle) = 1 THEN 1 ELSE 0 END AS IsFirstCycle
    FROM #temp_table
)
SELECT Id, Cycle, DateStamp, Name, IsFirstCycle
FROM FullTableRank
WHERE Id >= 14; -- 最后执行过滤条件

3. 写法生效原因 #

• CTE FullTableRank 先遍历整个临时表，给每个Name组里真正的第一条记录（按DateStamp、Cycle升序）标记IsFirstCycle=1，其余为0。
• 外层查询再加上Id>=14的过滤，此时Tom的Id=14记录因为全表中Tom的首条是Id=13，所以IsFirstCycle会是0，完全符合需求。

错误写法对比（问题根源） #

如果直接先加WHERE再分区排序，会导致过滤后的子集被当成完整组处理：

-- 错误示例：提前过滤导致Tom的Id=14被错误标记为1
SELECT 
    *,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY Name ORDER BY DateStamp, Cycle) = 1 THEN 1 ELSE 0 END AS IsFirstCycle
FROM #temp_table
WHERE Id >= 14;

这里WHERE先过滤掉了Tom的Id=13，OVER子句只能看到剩下的Id=14，所以错误地把它标记为该组的首条。

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