我有这两张表
DECLARE @Site TABLE (
SiteCode varchar(4) NOT NULL
);
INSERT INTO @Site (SiteCode)
VALUES
('S1'),
('S2'),
('S3'),
('S4')
DECLARE @SiteStatus TABLE (
SiteCode varchar(4) NOT NULL,
Year int NOT NULL,
Month varchar(10) NOT NULL,
UploadCount int NOT NULL
);
INSERT INTO @SiteStatus (SiteCode, Year, Month, UploadCount)
VALUES
('S1', 2024, 'March', 1),
('S1', 2024, 'April', 1),
('S1', 2024, 'May', 1),
('S1', 2024, 'June', 1),
('S2', 2024, 'March', 1),
('S2', 2024, 'April', 1),
('S2', 2024, 'May', 0),
('S2', 2024, 'June', 0),
('S3', 2024, 'March', 1),
('S3', 2024, 'April', 0)
我想编写一个将返回以下内容的查询
SELECT ... ?
'S1', 2024, 'March', 1
'S1', 2024, 'April', 1
'S1', 2024, 'May', 1
'S1', 2024, 'June', 1
'S2', 2024, 'March', 1
'S2', 2024, 'April', 1
'S2', 2024, 'May', 0
'S2', 2024, 'June', 0
'S3', 2024, 'March', 1
'S3', 2024, 'April', 0
'S3', 2024, 'May', 0
'S3', 2024, 'June', 0
'S4', 2024, 'March', 0
'S4', 2024, 'April', 0
'S4', 2024, 'May', 0
'S4', 2024, 'June', 0
基本上,为 SiteStatus 中缺少的所有站点日期组合添加零记录。
谢谢!
您可以
CTEs
首先获取所有Distinct
年/月组合,然后cross join
获取所有站点,结果可用于LEFT JOIN
site_status。但顺便说一句,最好将日期保存为日期,然后提取年份和月份,这样可以节省空间,并且有助于正确排序。
小提琴