如何用SQL Server 2016查询应存在但缺失的合规记录？

我来帮你搞定这个需求！要生成所有设施在指定日期范围内对应CertItem的每日应响应记录（包括已响应和未响应的），我们可以利用SQL Server 2016的递归CTE来生成日期序列，再结合表关联实现目标。具体方案如下：

核心思路 #

因为实际的CertCompliance表只存储了已响应的记录，我们需要先"补全"所有理论上应该存在的每日记录，再左连接已有的响应数据。步骤拆解：

• 用递归CTE生成指定日期范围内的每一天
• 关联设施和对应的证书项，确定每个证书的有效日期范围
• 将设施与日期做笛卡尔积，筛选出在证书有效期内的记录
• 左连接到实际合规表，填充已有的响应日期

完整SQL代码 #

DECLARE @StartDate DATE = '2018-05-01'; -- 替换为你的目标起始日期
DECLARE @EndDate DATE = '2018-05-10'; -- 替换为你的目标结束日期

-- 递归CTE生成日期序列（SQL Server 2016没有GENERATE_SERIES，用这个替代）
WITH DateRange AS (
    SELECT @StartDate AS ResponseDueDate
    UNION ALL
    SELECT DATEADD(DAY, 1, ResponseDueDate)
    FROM DateRange
    WHERE ResponseDueDate < @EndDate
),
-- 获取每个设施对应的证书项及有效日期范围
FacilityCertPairs AS (
    SELECT 
        fc.FacilityID,
        fc.FacCertificateID,
        ci.CertiItemID,
        ci.CertificateName,
        ci.StartDate AS CertStart,
        ci.EndDate AS CertEnd
    FROM FacilityCertificate fc
    INNER JOIN CertItems ci 
        ON fc.CertItemID = ci.CertiItemID
)
-- 生成所有理论应存在的记录，并关联已响应数据
SELECT 
    fcp.FacilityID,
    fcp.CertiItemID,
    fcp.CertificateName,
    dr.ResponseDueDate,
    cc.ResponseDate
FROM FacilityCertPairs fcp
CROSS JOIN DateRange dr
-- 确保日期同时在证书有效期和用户指定的范围内
WHERE dr.ResponseDueDate BETWEEN fcp.CertStart AND fcp.CertEnd
LEFT JOIN CertCompliance cc 
    ON cc.FacCertificateID = fcp.FacCertificateID
    AND cc.ResponseDueDate = dr.ResponseDueDate
ORDER BY fcp.FacilityID, dr.ResponseDueDate
OPTION (MAXRECURSION 0); -- 解除递归层数限制（日期范围超过100天必须加）

代码细节解释 #

1. DateRange CTE：递归生成从@StartDate到@EndDate的所有日期，解决了SQL Server 2016没有内置日期序列函数的问题。
2. FacilityCertPairs CTE：把设施证书关联表和证书项表结合，一次性拿到每个设施对应的证书ID、名称和有效日期，避免后续重复查询。
3. 主查询：
   • CROSS JOIN把每个设施和每一天组合，得到所有理论上的每日应响应记录
   • LEFT JOIN关联实际的合规表，已响应的记录会带出ResponseDate，未响应的则显示NULL
4. OPTION (MAXRECURSION 0)：默认递归CTE的最大层数是100，如果你的日期范围超过100天，必须加这个选项防止报错。

性能优化建议 #

如果涉及数千个设施和数百天的数据量，建议给这些表加索引：

• FacilityCertificate(FacilityID, CertItemID, FacCertificateID)：加速设施和证书的关联
• CertCompliance(FacCertificateID, ResponseDueDate)：加速响应记录的匹配

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