我有这两张表
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 中缺少的所有站点日期组合添加零记录。
谢谢!