如何根据列返回缺失的行，其中其他值在MSSQL中匹配

Question

我甚至不知道怎么问这个问题，所以我可以谷歌一下，所以我提前道歉。我有一个SQL表，它看起来像这样

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]，有4行col1=a。但是，不存在col1=b且col2=4的记录。

使用分组和count<4，我可以返回存在的行，但是我如何为一行(我缺失的记录)返回'b,4,name1'之类的东西

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

Answer 1

基本思想是创建一个交叉连接，然后通过测试过滤器来过滤掉遗漏的内容。

Schema (MSSQL 2019)

CREATE TABLE raw
([col1] varchar(1), [col2] int, [col3] varchar(5))
;

INSERT INTO raw
([col1], [col2], [col3])
VALUES
('a', 1, 'name1'),
('a', 2, 'name1'),
('a', 3, 'name1'),
('a', 4, 'name1'),
('b', 1, 'name1'),
('b', 2, 'name1'),
('b', 3, 'name1')
;

SQL查询

-- insert into raw -- uncomment to create missing records.
select candidate_base.* from ( 
select candidate.col1, base.col2, base.col3 from 
( select col1 from raw group by col1 ) candidate ,
( select col2, col3 from raw group by col2, col3 ) base
) candidate_base
left join raw filter
on  candidate_base.col1 = filter.col1
and candidate_base.col2 = filter.col2
and candidate_base.col3 = filter.col3
where filter.col1 is null 
GO

col1 | col2 | col3:——|——:|:----B | 4 | name1

db<此处小提琴>