如何在SQL中按类别补全缺失的记录？

当然可以实现你想要的效果！你当前的full outer join只是把两个表的现有记录做了合并，但没有生成每个基金对应所有标准日期的完整组合。我们需要先构建出所有日期和基金的可能配对，再关联原始数据来补全缺失值。

解决思路 #

1. 先提取所有唯一的基金（Fund）类别，生成一个独立的基金列表
2. 使用CROSS JOIN把标准日期表（dat2）和基金列表组合，得到每个基金对应所有日期的完整集合
3. 用LEFT JOIN关联原始数据（dat1），对于没有匹配到的记录，将val字段填充为0

修改后的SQL代码 #

WITH dat1 AS (
 SELECT CONVERT(date, DATEADD(day,+14,GETDATE()), 103) AS [date1], 'ABC' AS Fund, 2 AS val
 UNION
 SELECT CONVERT(date, GETDATE(), 103) AS [date1], 'BCG' AS Fund, 2 AS val
), dat2 AS (
 SELECT CONVERT(date, DATEADD(day,+14,GETDATE()), 103) AS [date]
 UNION
 SELECT CONVERT(date, GETDATE(), 103) AS [date]
), funds AS (
 -- 提取所有唯一的基金类别
 SELECT DISTINCT Fund FROM dat1
), full_date_fund AS (
 -- 生成所有日期和基金的组合
 SELECT d.[date], f.Fund
 FROM dat2 d
 CROSS JOIN funds f
)
SELECT 
 fdf.[date] AS date1,
 fdf.Fund,
 ISNULL(d1.val, 0) AS val,
 fdf.[date]
FROM full_date_fund fdf
LEFT JOIN dat1 d1 ON fdf.[date] = d1.date1 AND fdf.Fund = d1.Fund
ORDER BY fdf.[date], fdf.Fund;

执行结果说明 #

这段代码会生成你期望的输出：每个基金都会对应dat2里的所有日期，原始数据中不存在的记录会自动把val填充为0，完全匹配你想要的结果格式。

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