MSSQL中其余字段值匹配时如何基于指定列查询缺失行

现有业务表存在分组下固定枚举值缺失的情况，样例数据如下：

col1 col2 col3
a    1    name1
a    2    name1
a    3    name1
a    4    name1
b    1    name1
b    2    name1
b    3    name1

已知col2的合法取值固定为1-4四个值：当col1+col3的分组下未覆盖全部4个col2值时，需要返回具体缺失的记录。例如样例中col1=b的分组缺失col2=4的记录，预期返回格式为b,4,name1。
原有通过分组统计col2计数小于4的写法，只能查到存在记录的分组，无法定位具体缺失的条目，原有查询语句如下：

SELECT * FROM
(
SELECT T.COL1, T.COL3, COUNT(T.COL2) COL2_COUNT, STRING_AGG(T.COL2,',') COL2_LIST
FROM
(
SELECT F.COL1, F.COL3, F.COL2 FROM TBL F 
) T
GROUP BY T.COL1, T.COL3
) J WHERE J.COL2_COUNT < 4

核心逻辑：先构造所有理论上应该存在的(col1, col2, col3)全量组合，再和原表做匹配，匹配失败的记录就是缺失的条目，不需要依赖计数反推。

• 第一步：提取表中所有col1+col3的去重分组
• 第二步：和固定的col2合法枚举值做笛卡尔积，生成所有应该存在的完整记录
• 第三步：用全量理论记录左连原表，关联不到原表数据的就是缺失记录

参考SQL写法（兼容MySQL 8+、PostgreSQL、SQL Server 2017+等主流支持CTE的数据库）：

-- 构造col2的固定枚举值，如有单独存储合法值的维度表可直接替换该部分
WITH col2_enum AS (
    SELECT 1 AS col2 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
),
-- 提取全部分组
all_groups AS (
    SELECT DISTINCT col1, col3 FROM TBL
),
-- 生成理论上应存在的全部记录
full_expected AS (
    SELECT ag.col1, ce.col2, ag.col3
    FROM all_groups ag
    CROSS JOIN col2_enum ce
)
-- 查询缺失记录
SELECT fe.col1, fe.col2, fe.col3
FROM full_expected fe
LEFT JOIN TBL t
  ON fe.col1 = t.col1
 AND fe.col2 = t.col2
 AND fe.col3 = t.col3
WHERE t.col2 IS NULL;

上述语句在样例数据下执行，会直接返回b,4,name1的结果。如果后续col2的合法取值有调整，只需要修改col2_enum中的枚举值即可。

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