sql - 如何使用 SQL Server 在此查询中按天对结果进行分组？

Question

sql fiddle demo here

我有日记表的表结构:

 CREATE TABLE Diary
(
     [IdDiary] bigint, 
     [UserId] int,
     [IdDay] numeric(18,0),
     [IsAnExtraHour] bit
);

INSERT INTO Diary ([IdDiary],  [UserId],  [IdDay], [IsAnExtraHour])
values 
(51, 1409, 1, 0),
(52, 1409, 1, 1),
(53, 1409, 3, 0),
(54, 1409, 5, 0),
(55, 1409, 5, 1),
(56, 1408, 2, 0);

DiaryTimetable 表的结构:

CREATE TABLE DiaryTimetable
(
     [IdDiary] bigint, 
     [Hour] varchar(50)
);

  INSERT INTO DiaryTimetable ([IdDiary], [Hour])
VALUES
    (51, '09:00'),
    (51, '09:30'),
    (51, '10:00'),
    (51, '10:30'),
    (51, '11:00'),
    (52, '15:00'),
    (52, '15:30'),
    (52, '16:00'),
    (52, '16:30'),
    (52, '17:00'),
    (53, '11:00'),
    (53, '11:30'),
    (53, '12:00'),
    (53, '12:30'),
    (53, '13:00'),
    (54, '10:00'),
    (54, '10:30'),
    (54, '11:00'),
    (54, '11:30'),
    (54, '12:00'),
    (55, '16:00'),
    (55, '16:30'),
    (55, '17:00'),
    (55, '17:30'),
    (55, '18:00'),
    (56, '15:00'),
    (56, '15:30'),
    (56, '16:00'),
    (56, '16:30'),
    (56, '17:00');

我使用此查询来获取用户 ID 1409 的最大小时数和最短小时数，以获取每天的上类时间和下类时间。 idday 对应于一周中的第几天。例如 1 是星期一，2 是星期二等等...

 SELECT d.IdDiary, d.IdDay, MIN(Hour) as 'Start Time', MAX(Hour) as 'End Time', IsAnExtraHour
FROM Diary AS d
LEFT JOIN DiaryTimetable AS dt ON d.IdDiary = dt.IdDiary
where userid = 1409
GROUP BY d.IdDiary, d.IdDay, IsAnExtraHour

此查询给出以下结果:

我想要得到这个结果:

    Day       Start Time    End Time    Start Extra Time    End Extra Time
    -----     ----------    --------    ---------------     ---------------
   Monday       09:00         11:00        15:00                17:00
   Wednessday   11:00         13:00        
   Friday       10:00         12:00        16:00                18:00

我有一个列(IsAnExtraHour)，该列​​指示该行一天中是否有额外的工作时间，例如员工在周一 09:00 到 11:00 开始工作，然后在下午 15:00 再次工作到17:00，所以我想知道如何将这些时间分组在同一行，我希望我能够表达清楚，我接受建议谢谢。

Answer 1

SELECT  d.IdDay,
        MIN(CASE WHEN isAnExtraHour = 0 THEN hour END) as 'Start Time',
        MAX(CASE WHEN isAnExtraHour = 0 THEN hour END) as 'End Time',
        MIN(CASE WHEN isAnExtraHour = 1 THEN hour END) as 'Start Extra Time',
        MAX(CASE WHEN isAnExtraHour = 1 THEN hour END) as 'End Extra Time'
FROM    Diary AS d
LEFT JOIN
        DiaryTimetable AS dt
ON      dt.IdDiary = d.IdDiary
WHERE   userid = 1409
GROUP BY
        d.IdDay