SQL Server 2019高效查询保险表缺失指定日期记录

运行环境为SQL Server 2019，涉及两张业务表：

• DATES表：仅包含DATE字段，存储测试日期值为01/01/2022、02/01/2022
• INSURANCE_COVER表：包含INSURANCE_COVER_ID、INSURANCE_DATE两个字段，测试数据如下：
  • INSURANCE_COVER_ID = 1：对应INSURANCE_DATE为02/01/2022、03/01/2022
  • INSURANCE_COVER_ID = 2：对应INSURANCE_DATE为31/12/2021、01/01/2022

需求为查询所有保险ID未在DATES表中匹配到的日期，返回缺失的DATE值及对应保险ID，预期返回两列DATE、INSURANCE_ID，结果为01/01/2022对应INSURANCE_ID=1，02/01/2022对应INSURANCE_ID=2。原有while循环实现性能极差，需要更高性能的集合式实现方案。

不要使用行级循环处理，基于SQL集合运算逻辑实现，性能远高于循环写法，核心逻辑分两步：

1. 用交叉连接生成所有「保险ID + DATES表日期」的全量可能组合
2. 用NOT EXISTS反连接，过滤掉INSURANCE_COVER表中已经存在的配对，剩余结果就是需要的缺失记录

参考实现代码：

SELECT
    d.DATE,
    ic.INSURANCE_COVER_ID AS INSURANCE_ID
FROM DATES d
-- 交叉连接拿到所有不重复的保险ID，避免原表重复数据放大结果集
CROSS JOIN (
    SELECT DISTINCT INSURANCE_COVER_ID
    FROM INSURANCE_COVER
) ic
-- 过滤掉已经存在的保险+日期配对
WHERE NOT EXISTS (
    SELECT 1
    FROM INSURANCE_COVER ic_exist
    WHERE ic_exist.INSURANCE_COVER_ID = ic.INSURANCE_COVER_ID
      AND ic_exist.INSURANCE_DATE = d.DATE
)
ORDER BY d.DATE, ic.INSURANCE_COVER_ID;

性能优化建议 #

如果表数据量较大，可以通过建索引进一步提升执行效率：

• 给DATES表的DATE字段建主键/唯一索引
• 给INSURANCE_COVER表建联合索引(INSURANCE_COVER_ID, INSURANCE_DATE)，反关联时可以直接走索引定位，不需要回表扫描数据

按照给出的测试数据执行上述代码，返回结果和预期完全一致。

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