Início / user-221585

Chris Harrington's questions

Martin Hope
Chris Harrington
Asked: 2024-04-02 01:41:29 +0800 CST
  • 7

Eu tenho essas duas tabelas

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)

E eu quero escrever uma consulta que retornará o seguinte

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

Basicamente, adicione zero registros para todos os combos de data do site ausentes no SiteStatus.

Obrigado!

sql-server