SQL查询：筛选单个Test_ID时如何获取正确的TEST_NUMB（不使用临时表）

这是个很常见的窗口函数使用误区，我来帮你理清问题根源并给出高效的解决方案：

问题根源 #

你之前的查询是先筛选数据，再计算窗口函数——当你用WHERE Test_ID = X过滤后，每个Child_ID的分区里只剩这一条数据，所以RANK()自然返回1。但你需要的是这个测试在该Child的所有测试序列中的真实序号，所以必须先计算所有数据的序号，再进行筛选。

无需临时表的高效解决方案 #

直接用子查询（或CTE）先完成全量的序号计算，再在外层筛选目标Test_ID：

基础版本（筛选单个Test_ID） #

SELECT *
FROM (
    SELECT 
        Child_ID, 
        Test_ID, 
        Test_Date,
        -- 先对所有数据按Child分区计算真实序号
        RANK() OVER (PARTITION BY Child_ID ORDER BY Test_Date ASC, Test_ID ASC) AS TEST_NUMB
    FROM Test
) AS RankedTests
-- 外层再筛选你需要的Test_ID
WHERE Test_ID = 1

扩展版本（支持多条件筛选） #

如果需要同时指定Child_ID或多个Test_ID，直接在外层添加条件即可：

SELECT *
FROM (
    SELECT 
        Child_ID, 
        Test_ID, 
        Test_Date,
        RANK() OVER (PARTITION BY Child_ID ORDER BY Test_Date ASC, Test_ID ASC) AS TEST_NUMB
    FROM Test
) AS RankedTests
WHERE Test_ID IN (1, 2) 
  AND Child_ID = 2560249

百万级数据的性能优化 #

针对百万级数据，建议创建覆盖索引来加速窗口函数的计算：

CREATE NONCLUSTERED INDEX IX_Test_ChildID_TestDate_TestID 
ON Test(Child_ID, Test_Date, Test_ID)
-- 如果查询需要其他字段，在这里INCLUDE进去
INCLUDE (/* 比如其他需要返回的字段 */)

这个索引会让数据库直接从索引里获取计算窗口函数所需的所有数据，避免回表查询，大幅提升性能。

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