我的日记表具有以下表结构:
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,所以我想知道如何将这些时间分组在同一行中,希望我已经能够表达清楚,我接受建议谢谢。
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句