sql HAVING max(count()) 返回零行
我正在尝试使用重叠课程安排的教室,我的表格:课程:
I'm trying to get class rooms with overlap course schedule, my tables: courses:
COURSE_ID NAME
11 matematika
22 logika
33 himiya
44 sport
55 algoritmika
66 hedva
77 algebra linearit
日程:
ID COURSE_ID ID_ROOM DAY HOUR
1 11 105 Mon 10am
2 11 105 Wen 10am
3 11 105 Thu 10am
4 22 105 Mon 10am
5 22 205 Wen 10am
6 22 105 Thu 10am
7 33 305 Mon 11am
8 33 105 Mon 10am
教室:
ID_ROOM LOCATION CAPACITY
105 A 20
205 B 10
305 C 30
我的sql是:
select class_room.ID_ROOM as crid, class_room.LOCATION, schedule.DAY as d, schedule.HOUR as h, count(courses.COURSE_ID) as count
from schedule
natural join class_room
natural join courses
group by crid, d, h
order by count desc;
我得到:
crid LOCATION d h count
105 A Mon 10am 3
105 A Thu 10am 2
305 C Mon 11am 1
105 A Wen 10am 1
205 B Wen 10am 1
但我只需要显示计数的所有最大值(现在有 1 个这样的行).我试过了
But I need to show all maximal values of count only (1 such row for now). I tryed
select class_room.ID_ROOM as crid, class_room.LOCATION, schedule.DAY as d, schedule.HOUR as h, count(courses.COURSE_ID) as count
from schedule
natural join class_room
natural join courses
group by crid, d, h
having max(count)
order by count desc;
但是是返回空表.怎么了?或者,也许是另一种解决方案的建议,以获得我需要的东西?
But is return empty table. What is wrong? Or, maybe suggestion of another solution, to get what I need?
推荐答案
以下将返回匹配最大数量的所有组
Following would return all groups matching the max count
SQL 小提琴
select class_room.ID_ROOM as crid
, class_room.LOCATION
, schedule.DAY as d
, schedule.HOUR as h
, count(courses.COURSE_ID) as count
from schedule
natural join class_room
natural join courses
group by
crid, d, h
having count(*) = (
select max(count)
from (
select count(courses.COURSE_ID) as count
from schedule
natural join class_room
natural join courses
group by
id_room, day, hour
) maxcount
)
相关文章