Início / dba / Perguntas / 338221
Accepted
Chris Harrington
Asked: 2024-04-02 01:41:29 +0800 CST

Consulta para adicionar registros para linhas pai que estão faltando nos detalhes

  • 772

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

2 respostas

  1. Best Answer

    Você pode usar CTEspara obter primeiro todas Distinctas combinações de ano/mês, depois cross joinisso com todos os sites e o resultado pode ser usado para LEFT JOINsite_status.

    Mas, como observação, seria melhor salvar as datas como datas e depois extrair o ano e o mês, o que economiza espaço e ajudaria a classificar corretamente.

     WITH y_m AS ( 
SELECT DISTINCT Year, Month FROM @SiteStatus 
 )
,  C_j AS ( SELECT * FROM @Site  CROSS JOIN y_m )
SELECT c.SiteCode, c.year, c.month, COALESCE(sis.UploadCount,0)
 FROM C_j c  LEFT JOIN @SiteStatus AS sis 
  On c.SiteCode = sis.SiteCode AND c.year = sis.year AND c.month = sis.month
ORDEr BY c.SiteCode, c.year, c.month
    Código do site ano mês (Sem nome de coluna)
    S1 2024 abril 1
    S1 2024 Junho 1
    S1 2024 Marchar 1
    S1 2024 Poderia 1
    S2 2024 abril 1
    S2 2024 Junho 0
    S2 2024 Marchar 1
    S2 2024 Poderia 0
    S3 2024 abril 0
    S3 2024 Junho 0
    S3 2024 Marchar 1
    S3 2024 Poderia 0
    S4 2024 abril 0
    S4 2024 Junho 0
    S4 2024 Marchar 0
    S4 2024 Poderia 0

    violino

    • 4 
  2. WITH SiteMonths
AS
(
    SELECT DISTINCT S.[SiteCode], YM.[Year], YM.[Month]
    FROM @Site S
        CROSS JOIN @SiteStatus YM
)
SELECT M.[SiteCode], M.[Year], M.[Month], COALESCE(S.UploadCount, 0) AS UploadCount
FROM SiteMonths M
    LEFT JOIN @SiteStatus S
        ON M.[SiteCode] = S.[SiteCode]
            AND M.[Year] = S.[Year]
            AND M.[Month] = S.[Month]
ORDER BY SiteCode, [Year], DATEPART(month, CONCAT(M.[Month], '01 1900'));
    • 2

relate perguntas