使用FIRST_VALUE填充Location空值并补全缺失行的SQL查询需求

问题核心 #

基于两张临时表生成每个Location对应的全量10条Document记录：

• #Baseline：固定10种Document，Type='None'，Location为NULL
• #Data：部分Document的Type和具体Location值
  要求：已存在的记录复用#Data的Type，缺失记录Type设为'None'，且每个Location都要有完整的10条记录。

最优实现方案（交叉连接+左连接） #

这种方式无需复杂窗口函数，逻辑直观高效，能自动适配任意数量的Location：

-- 提取所有唯一的Location
WITH UniqueLocations AS (
    SELECT DISTINCT Location
    FROM #Data
)
-- 为每个Location生成全量Document记录，匹配已有数据
SELECT
    ul.Location,
    b.Document,
    -- 优先用#Data的Type，无匹配则用Baseline的'None'
    COALESCE(d.Type, b.Type) AS Type
FROM UniqueLocations ul
-- 交叉连接得到每个Location+Document的基础组合
CROSS JOIN #Baseline b
-- 左连接匹配已有的Location+Document记录
LEFT JOIN #Data d
    ON ul.Location = d.Location
    AND b.Document = d.Document
ORDER BY ul.Location, b.Document;

FIRST_VALUE的错误修正 #

你之前用FIRST_VALUE只显示单个Location的问题，根源是Partition By参数未同时包含Location和Document，导致窗口函数跨Location取了第一条值。如果一定要用窗口函数实现，修正后的写法如下：

WITH LocationDocumentPairs AS (
    -- 生成所有Location+Document的组合
    SELECT
        d.Location,
        b.Document,
        b.Type AS DefaultType,
        d.Type AS ExistingType
    FROM #Data d
    CROSS JOIN #Baseline b
)
SELECT
    Location,
    Document,
    -- 在同一个Location+Document组内取非空的Type，无则用默认值
    COALESCE(
        FIRST_VALUE(ExistingType) OVER (
            PARTITION BY Location, Document 
            ORDER BY CASE WHEN ExistingType IS NOT NULL THEN 0 ELSE 1 END
        ),
        DefaultType
    ) AS Type
FROM LocationDocumentPairs
GROUP BY Location, Document, DefaultType, ExistingType
ORDER BY Location, Document;

这里的关键是PARTITION BY Location, Document，确保窗口函数仅在当前Location的当前Document组内计算，不会跨组干扰。

方案对比 #

• 交叉连接+左连接：代码更简洁，执行效率更高，适合大多数场景
• 窗口函数方案：适合需要更复杂排序/取值逻辑的场景，但代码相对繁琐

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