基于SortOrder为每个Key多列匹配对应记录的T-SQL优化方案问询

问题说明 #

现有一张表，结构如下：

• Key：int类型，非空
• Column1至Column9（示例简化为Column1-Column3）：varchar(30)类型，允许为空
• SortOrder：int类型，非空

需求：结果集中每个Key仅保留一行，Column1到Column9的值需取原表中按SortOrder升序排列后该列的第一个非空值。表数据量在10-20万行，要求高性能，最好只读取表一次，可使用分析函数优化，同时需要索引建议。

原表示例数据 #

期望结果 #

现有方案的问题 #

当前使用的查询虽能得到正确结果，但可读性差且性能低下——每个列的子查询都会重复扫描表，数据量较大时效率极低：

WITH Keys AS (SELECT Key FROM MyTable GROUP BY Key)
SELECT
Key,
(SELECT TOP(1) A.Column1 FROM MyTable A WHERE A.Column1 IS NOT NULL AND A.Key=Keys.Key ORDER BY SortOrder) AS Column1,
(SELECT TOP(1) A.Column2 FROM MyTable A WHERE A.Column2 IS NOT NULL AND A.Key=Keys.Key ORDER BY SortOrder) AS Column2,
(SELECT TOP(1) A.Column3 FROM MyTable A WHERE A.Column3 IS NOT NULL AND A.Key=Keys.Key ORDER BY SortOrder) AS Column3
FROM Keys

优化后的解决方案 #

以下两种方案均只扫描表一次，性能和可读性都更优：

方案1：使用FIRST_VALUE窗口函数 #

利用FIRST_VALUE按Key分区，优先排序非空值，直接提取每个列的首个有效数据，最后去重得到每个Key的唯一行：

WITH RankedData AS (
    SELECT
        [Key],
        FIRST_VALUE(Column1) OVER (PARTITION BY [Key] ORDER BY CASE WHEN Column1 IS NOT NULL THEN 0 ELSE 1 END, SortOrder) AS Column1,
        FIRST_VALUE(Column2) OVER (PARTITION BY [Key] ORDER BY CASE WHEN Column2 IS NOT NULL THEN 0 ELSE 1 END, SortOrder) AS Column2,
        FIRST_VALUE(Column3) OVER (PARTITION BY [Key] ORDER BY CASE WHEN Column3 IS NOT NULL THEN 0 ELSE 1 END, SortOrder) AS Column3
        -- 依次添加Column4到Column9的对应语句
    FROM MyTable
)
SELECT DISTINCT
    [Key],
    Column1,
    Column2,
    Column3
    -- 对应添加Column4到Column9
FROM RankedData;

方案2：使用ROW_NUMBER标记首行后聚合 #

通过ROW_NUMBER为每个列的首个非空行标记序号，再用聚合函数提取对应值：

WITH ColumnFlags AS (
    SELECT
        [Key],
        Column1,
        Column2,
        Column3,
        ROW_NUMBER() OVER (PARTITION BY [Key] ORDER BY CASE WHEN Column1 IS NOT NULL THEN 0 ELSE 1 END, SortOrder) AS rn1,
        ROW_NUMBER() OVER (PARTITION BY [Key] ORDER BY CASE WHEN Column2 IS NOT NULL THEN 0 ELSE 1 END, SortOrder) AS rn2,
        ROW_NUMBER() OVER (PARTITION BY [Key] ORDER BY CASE WHEN Column3 IS NOT NULL THEN 0 ELSE 1 END, SortOrder) AS rn3
        -- 对应添加rn4到rn9的标记语句
    FROM MyTable
)
SELECT
    cf.[Key],
    MAX(CASE WHEN cf.rn1 = 1 THEN cf.Column1 END) AS Column1,
    MAX(CASE WHEN cf.rn2 = 1 THEN cf.Column2 END) AS Column2,
    MAX(CASE WHEN cf.rn3 = 1 THEN cf.Column3 END) AS Column3
    -- 对应添加Column4到Column9的CASE聚合语句
FROM ColumnFlags cf
GROUP BY cf.[Key];

索引建议 #

创建覆盖索引，让查询直接从索引获取所有需要的数据，避免回表：

CREATE NONCLUSTERED INDEX IX_MyTable_Key_SortOrder ON MyTable ([Key], SortOrder)
INCLUDE (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9);

该索引以Key和SortOrder为排序键，包含所有业务列，完美匹配窗口函数的分区和排序需求，能大幅提升查询效率。

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