sql - 如何在此查询中获取每一天的 MAX(Hour) 和 The MIN(Hour)？

Question

我有这样的日记表结构:

CREATE TABLE Diary
(
     [IdDiary] bigint, 
     [IdDay] numeric(18,0)
);

INSERT INTO Diary ([IdDiary], [IdDay])
values 
(51, 1),
(52, 2),
(53, 5);

还有表 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'),
    (51, '11:30'),
    (52, '11:00'),
    (52, '11:30'),
    (52, '12:00'),
    (52, '12:30'),
    (52, '13:00'),
    (52, '13:30'),
    (53, '15:00'),
    (53, '15:30'),
    (53, '16:00'),
    (53, '16:30');

表Diary包含一个IdDiary，IdDay是天数，例如:

Monday --> 1
Tuesday --> 2
Wednesday --> 3
Thursday --> 4
Friday --> 5
Saturday --> 6
Sunday --> 7

DiaryTimetable 表包含日记和小时。我想获取 DiaryTimetable 表中每天出现的最大小时数和最小小时数，如果我输入此查询，则结果将仅为所有查询的最大小时数和最小小时数:

select MAX(Hour), MIN(Hour) from DiaryTimetable
inner join Diary on
DiaryTimetable.IdDiary = Diary.IdDiary

我需要的 wat 结果应该是这样的:

IdDiary  IdDay   Min Hour   Max Hour
-----    -----   --------   ---------
51        1      09:00       11:30
52        2      11:00       13:30
53        5      15:00       16:30

我怎样才能得到这个，谢谢？

SQL FIDDLE DEMO HERE

Answer 1

您使用 GROUP BY 子句:

SELECT d.IdDiary, d.IdDay, MIN(Hour), MAX(Hour)
FROM Diary AS d
LEFT JOIN DiaryTimetable AS dt ON d.IdDiary = dt.IdDiary
GROUP BY d.IdDiary, d.IdDay

Demo here