如何在此查询中获取每天的最大(小时)和最小(小时)? [英] How to get the MAX(Hour) and The MIN(Hour) for each day in this query?

问题描述

我有表日记的这种结构:

I have this structure of table Diary:

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

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

表DiaryTimetable的另一种结构:

And this other structure for 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'),
    (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是天数，例如:

The table Diary contains an IdDiary and the IdDay is the number of day, for example:

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

表DiaryTimetable包含iddiary和小时. 我想获取DiaryTimetable表中每天出现的最大小时和最小小时，如果出现在Diary表中，如果我输入此查询，结果将仅为所有查询的最大小时和最小小时:

The table DiaryTimetable contains the iddiary, and the hour. I want want to get the max hour and the min hour in the table DiaryTimetable for each day appears in the Diary table, If I put this query the result will be only the max hour and the min hour for all the query:

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

我需要的wat结果将是这样的:

The result for wat I need will be something like that:

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

谢谢，我怎么能得到这个?

How can I get this, thanks?

此处是SQL演示版

推荐答案

您使用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

此处演示

这篇关于如何在此查询中获取每天的最大(小时)和最小(小时)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！