SQL不修改底层数据时如何补全SELECT结果中的MISSING字段

解决方案 #

完全可以通过自连接实现需求，无需CTE，可直接用于创建新视图，逻辑如下：

实现思路 #

• 先从原视图中提取所有非异常的正常行（即PartNumber、PartDescription、ProjectDescription都不为MISSING的行）作为维度映射表，这部分行的ProjectDescription值和异常行的PartName值一一对应。
• 将原视图和上述维度映射表做左关联，关联条件为原视图.PartName = 维度映射表.ProjectDescription。
• 字段取值用COALESCE（兼容绝大多数数据库，Oracle可替换为NVL，SQL Server可替换为ISNULL）做优先级判断：如果原行对应字段不为MISSING就用原值，否则取关联得到的维度映射表的对应字段值。

示例SQL代码 #

CREATE VIEW 修复后的视图名 AS
SELECT
  COALESCE(NULLIF(t1.PartNumber, 'MISSING'), t2.PartNumber) AS PartNumber,
  COALESCE(NULLIF(t1.PartDescription, 'MISSING'), t2.PartDescription) AS PartDescription,
  COALESCE(NULLIF(t1.ProjectDescription, 'MISSING'), t2.ProjectDescription) AS ProjectDescription,
  t1.PartName,
  t1.Cost
FROM 原视图名 t1
LEFT JOIN (
  SELECT DISTINCT PartNumber, PartDescription, ProjectDescription
  FROM 原视图名
  WHERE PartNumber != 'MISSING'
    AND PartDescription != 'MISSING'
    AND ProjectDescription != 'MISSING'
) t2 ON t1.PartName = t2.ProjectDescription

注意事项 #

• 子查询里加DISTINCT是为了避免正常行重复导致关联后数据行数翻倍，如果你能确认正常行的ProjectDescription唯一，可去掉。
• 如果你的数据库不支持NULLIF，可以把判断逻辑写在CASE WHEN里，效果一致。
• 生成的新视图所有字段无缺失值，可直接用于GROUP BY统计。

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