如何使用SQL Server在此查询中按天分组结果？

sql fiddle demo here

sql小提琴演示在这里

I have this table structure for Diary table:

我有这个表格结构的日记表：

 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);

And this structure for DiaryTimetable table:

而这个结构为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');

I used this query to get the max hour and the min hour for the userid 1409, to get for each day the time thats enter and the time thats leave the work. The idday correspond with the number of the day of the week. For example 1 is monday, 2 is tuesday etc...

我使用此查询来获取用户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

This query give this result:

此查询提供此结果：

I want to get this result:

我想得到这个结果：

    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

I have a column (IsAnExtraHour) this column indicates if this row has extra hours in a day, for example an employe start work in monday at 09:00 to 11:00 and then works again in the afternoon at 15:00 to 17:00, so I want to know how can I group this hours in the same row, I hope I've been able to express clearly, I accept suggestions thanks.

我有一个列（IsAnExtraHour）此列表示此行是否在一天中有额外的小时数，例如员工在星期一09:00到11:00开始工作，然后在下午15:00到17再次工作： 00，所以我想知道如何将这个小时分组在同一行，我希望我能够清楚地表达，我接受建议表示感谢。

4 个解决方案

#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

#2

I use the code from @Quassnoi and I added this:

我使用@Quassnoi的代码，我添加了这个：

SELECT DATENAME(weekday, d.idday-1) as 'Day' ,
       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

I hope this help someone, thanks all for your answers.

我希望这能有所帮助，感谢大家的回答。

#3

The way I'd approach this would be to work out your standard and extra hours separately, something like this;

我接近这个的方法是分别计算你的标准和额外时间，就像这样;

SELECT d.IdDay
    ,MIN(dt.StartTime) AS 'Start Time'
    ,MAX(dt.EndTime) AS 'End Time'
    ,MIN(ex.StartTime) AS 'Start Extra Time'
    ,MAX(ex.EndTime) AS 'End Extra Time'
FROM Diary AS d
LEFT JOIN (
    SELECT IdDiary
        ,MIN(Hour) AS StartTime
        ,MAX(Hour) AS EndTime
    FROM DiaryTimetable
    GROUP BY IdDiary
    ) AS dt ON d.IdDiary = dt.IdDiary
    AND d.IsAnExtraHour = 0
LEFT JOIN (
    SELECT IdDiary
        ,MIN(Hour) AS StartTime
        ,MAX(Hour) AS EndTime
    FROM DiaryTimetable
    GROUP BY IdDiary
    ) AS ex ON d.IdDiary = ex.IdDiary
    AND d.IsAnExtraHour = 1
WHERE userid = 1409
GROUP BY d.IdDay

#4

You can first get non-extra hours and left join extra hours to them by UserId and IdDay. Like this:

您可以先获得非额外时间，然后通过UserId和IdDay向他们加入额外的小时。喜欢这个：

SELECT
    CASE BaseHours.IdDay
        WHEN 1 THEN 'Monday' 
        WHEN 2 THEN 'Tuesday' 
        WHEN 3 THEN 'Wednesday' 
        WHEN 4 THEN 'Thursday' 
        WHEN 5 THEN 'Friday' 
        WHEN 6 THEN 'Saturday' 
        WHEN 7 THEN 'Sunday' 
    END AS [WeekDay],
    MIN(BaseTimeTable.Hour) as 'Start Time',
    MAX(BaseTimeTable.Hour) as 'End Time',
    MIN(ExtraTimeTable.Hour) as 'Start Extra Time',
    MAX(ExtraTimeTable.Hour) as 'End Extra Time'
FROM Diary AS BaseHours
LEFT JOIN Diary ExtraHours
    ON ExtraHours.UserId = BaseHours.UserId
        AND ExtraHours.IdDay = BaseHours.IdDay AND ExtraHours.IsAnExtraHour = 1
JOIN DiaryTimetable AS BaseTimeTable
    ON BaseHours.IdDiary = BaseTimeTable.IdDiary
LEFT JOIN DiaryTimetable AS ExtraTimeTable
    ON ExtraHours.IdDiary = ExtraTimeTable.IdDiary
WHERE BaseHours.Userid = 1409 AND BaseHours.IsAnExtraHour = 0
GROUP BY BaseHours.IdDay