首先,我认为您不必要地使用了子选择查询(
Derived Table
)获取当前结果。由于您没有对子查询结果进行任何进一步的筛选,因此您可以取消子查询,仍然可以更有效地获得相同的结果:
SELECT
d.dormid,
d.student_capacity,
COUNT(d.dormid) AS num
FROM Dorm AS d
LEFT JOIN Has_amenity AS h
ON h.dormid = d.dormid
GROUP BY d.dormid, d.student_capacity
现在,你基本上需要
Dense_Rank()
功能(排名1的宿舍设施最多)。
Window functions
仅在
MySQL versions 8.0.2 and onwards
.
在版本中
mysql<8.0.2版
我们可以利用
User-defined variables
,计算相同的值。这种方法的基本要点是在派生表中按要求的顺序获取结果集。在这种情况下,我们关注的是排名最高的宿舍;因此我们得到的结果集按
num
.
现在,我们使用两个会话变量来存储前一行的值,以便确定当前行的值,在
Select
条款。我们基本上存储前一行
rank
和
号码
价值观;以及
if
当前行的
号码
值与前一行不同,我们增加
等级
.
最后,再次将这个结果用作派生表,我们只需要考虑那些
rank = 1
(使用)
Where
条款。)
尝试以下查询(适用于所有MySQL版本):
SELECT dt2.*
FROM
(
SELECT
dt.dormid,
dt.student_capacity,
@drank := IF(@n <> dt.num, @drank + 1, @drank) AS rank,
@n := dt.num AS num
FROM
(
SELECT
d.dormid,
d.student_capacity,
COUNT(d.dormid) AS num
FROM Dorm AS d
LEFT JOIN Has_amenity AS h
ON h.dormid = d.dormid
GROUP BY d.dormid, d.student_capacity
ORDER BY num DESC
) AS dt
CROSS JOIN (SELECT @drank := 0,
@n := 0) AS user_init_vars
) AS dt2
WHERE dt2.rank = 1