sql - 填充表 SQL 中缺失的行

Question

我有一份报告需要每个案例的前 18 个 ID 代码。有些案例只有全部 18 行，有些案例只有几行。这是输出示例:

Case       idcode          value
 2            3             122
 2            6              52 
 2            15            121
 3            1             111 
 3            3             555
 3            6             322

我需要的输出是每条记录 18 行(idcodes 1-18)，如果添加了值，则将“无”作为值。如果我事先不知道缺少哪些行，添加缺失行的最佳方法是什么？

这是我的查询:

SELECT 
    rcl.CaseCaseId as Case, cce.StringValue as Value, cce.CorpIdCodeId as idcode
FROM         
    CaseIdCodeEntry AS cce 
INNER JOIN
    CorpIdCodes AS cid ON cce.CorpIdCodeId = cid.CorpIdCodeId 
INNER JOIN
    PhdRpt.ReportCaseList_542 AS rcl ON cce.CaseCaseId = rcl.CaseCaseId
WHERE
    (cce.CorpIdCodeId < 19)

Answer 1

我会使用递归 CTE 自动生成 1-18 的编号列表，然后从中进行 LEFT JOIN。然后使用 CASE 语句调整 Value 字段。

;WITH cte AS
(   SELECT DISTINCT CaseCaseId AS CaseID, 1 AS idcode
    FROM PhdRpt.ReportCaseList_542 UNION ALL
    SELECT CaseID, idcode+1 FROM cte WHERE idcode < 18 )
SELECT  cte.CaseID AS [Case], 
        CASE WHEN cce.CorpIdCodeId IS NULL THEN 'None' ELSE cce.StringValue END AS Value, 
        cte.idcode AS idcode
FROM cte
LEFT JOIN CaseIdCodeEntry cid ON cid.CorpCodeId = cte.idcode
LEFT JOIN CorpIdCodes cid ON cce.CorpIdCodeId = cid.CorpIdCodeId
LEFT JOIN PhdRpt.ReportCaseList_542 rcl ON cce.CaseCaseId = rcl.CaseCaseId