如何查询tblA中相对于tblDate缺失的[Name, Date]记录

需求：找出tblA中缺失的[Name, Date]记录，即tblDate中存在但tblA中无对应行的日期与名称组合。

表结构与测试数据 #

表定义 #

CREATE TABLE [dbo].[tblA]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Date] [date] NULL,
    [Val] [nvarchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblDate] 
(
    [Date] [date] NULL
) ON [PRIMARY]
GO

插入测试数据 #

SET IDENTITY_INSERT [dbo].[tblA] ON 
GO

INSERT [dbo].[tblA] ([Id], [Name], [Date], [Val])
VALUES
(3, N'A', CAST(N'2023-12-22' AS Date), N'data'),
(4, N'A', CAST(N'2023-12-23' AS Date), N'data'),
(5, N'B', CAST(N'2023-12-22' AS Date), N'data'),
(6, N'B', CAST(N'2023-12-23' AS Date), N'data'),
(7, N'A', CAST(N'2023-12-27' AS Date), N'data'),
(8, N'A', CAST(N'2023-12-28' AS Date), N'data')
GO

SET IDENTITY_INSERT [dbo].[tblA] OFF
GO

INSERT [dbo].[tblDate] ([Date])
VALUES
(CAST(N'2023-12-22' AS Date)),
(CAST(N'2023-12-23' AS Date)),
(CAST(N'2023-12-24' AS Date)),
(CAST(N'2023-12-25' AS Date)),
(CAST(N'2023-12-26' AS Date)),
(CAST(N'2023-12-27' AS Date)),
(CAST(N'2023-12-28' AS Date))
GO

当前数据 #

tblA数据：

Id  Name    Date        Val
-----------------------------
3   A       2023-12-22  data
4   A       2023-12-23  data
5   B       2023-12-22  data
6   B       2023-12-23  data
7   A       2023-12-27  data
8   A       2023-12-28  data

tblDate数据：

Date
----------
2023-12-22
2023-12-23
2023-12-24
2023-12-25
2023-12-26
2023-12-27
2023-12-28

预期结果 #

Name    Date
-------------------
A       2023-12-24
B       2023-12-24
A       2023-12-25
B       2023-12-25
A       2023-12-26
B       2023-12-26
B       2023-12-27
B       2023-12-28

错误语句分析 #

你尝试的语句逻辑有误：

select d.Date, a.Name
from dbo.tblDate d
full join dbo.tblA a on d.Date = a.Date
where a.Date is null

该语句仅能找出tblDate中没有任何tblA记录的日期，但无法针对每个Name单独排查缺失的日期，因此得不到预期结果。

正确查询语句 #

核心思路：先生成所有可能的Name与Date组合（通过唯一Name列表与tblDate的交叉连接），再排除tblA中已存在的组合，即可得到缺失的记录。

SELECT 
    names.Name,
    d.Date
FROM 
    -- 获取tblA中所有唯一的Name
    (SELECT DISTINCT Name FROM dbo.tblA) names
-- 交叉连接生成所有Name与Date的可能组合
CROSS JOIN 
    dbo.tblDate d
-- 左连接tblA，匹配已存在的组合
LEFT JOIN 
    dbo.tblA a ON names.Name = a.Name AND d.Date = a.Date
-- 筛选出没有匹配的组合（即缺失的记录）
WHERE 
    a.Id IS NULL
-- 按日期和名称排序，与预期结果一致
ORDER BY 
    d.Date, names.Name;

执行该语句后，即可得到与预期完全一致的缺失记录列表。

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