子查詢行上的 Sql Group by

我有這張表： ArretProductionJournee (Id, DateArret, HeureDebut, HeureFin, EnumArret) 範例：

DateArret   ||HeureDebut                 ||HeureFin                 ||EnumArret
2020-11-30  ||2020-11-30 14:00:00.000    ||2020-11-30 15:00:00.000  ||PS
2020-11-30  ||2020-11-30 16:00:00.000    ||2020-11-30 17:00:00.000  ||HI

我想對每個 EnumArret 列中的 datediff(HeureDebut,HeureFin) 求和，所以我執行這個查詢：

SELECT ArretProductionJournee.DateArret, 
(select
sum (datediff(minute,ArretProductionJournee.HeureDebut, 
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'HI')as HI,

(select
sum (datediff(minute,ArretProductionJournee.HeureDebut, 
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'PS') as PS

FROM            dbo.ArretProductionJournee
               where ArretProductionJournee.EnumArret Like 'HI'OR 
               ArretProductionJournee.EnumArret Like 'PS'
group by  ArretProductionJournee.EnumArret, dbo.ArretProductionJournee.DateArret

結果 ：

DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   NULL
2020-11-30  ||60   ||   NULL
2020-11-30  ||NULL ||   60

我想要的結果是按日期和 enumArret 對總和進行分組：

DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   0
2020-11-30  ||60   ||   60

而不是子查詢，使用條件聚合：

SELECT
     DateArret
   , SUM(CASE EnumArret WHEN 'HI' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS HI
   , SUM(CASE EnumArret WHEN 'PS' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS PS
FROM dbo.ArretProductionJournee
WHERE EnumArret IN('HI', 'PS')
GROUP BY
   DateArret
ORDER BY
   DateArret;

下面是更完整的範例數據和架構，以匹配您想要的結果。

CREATE TABLE dbo.ArretProductionJournee(
    ID int NOT NULL IDENTITY CONSTRAINT PK_ArretProductionJournee PRIMARY KEY
   ,DateArret date NOT NULL
   ,HeureDebut datetime2(3) NOT NULL
   ,HeureFin datetime2(3) NOT NULL
   ,EnumArret char(2) NOT NULL
);
INSERT INTO dbo.ArretProductionJournee(DateArret, HeureDebut, HeureFin, EnumArret)
VALUES
    ('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:05:00.000', 'HI')
   ,('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:07:00.000', 'HI')
   ,('2020-11-30', '2020-11-30 14:00:00.000', '2020-11-30 15:00:00.000', 'PS')
   ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'HI')
   ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'XX');

引用自：https://dba.stackexchange.com/questions/279377