如何使用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

4 个解决方案

#1

更多相关文章

随机推荐