返回MySQL中最大值(多个)的行

mreddy • 5 年前 • 1453 次点击

我有以下问题:

select
    a.dormid, a.student_capacity, count(*) as num
from
    (select
       d.dormid, 
       d.dorm_name, 
       d.student_capacity, 
       h.amenid
     from
       (Dorm d left join Has_amenity h 
       on d.dormid=h.dormid)
    ) a
group by a.dormid, a.student_capacity

结果如下:

+--------+------------------+-----+
| dormid | student_capacity | num |
+--------+------------------+-----+
|    109 |              128 |   8 |
|    104 |              256 |   3 |
|    160 |              400 |  12 |
|    100 |               85 |   5 |
|    117 |               40 |   1 |
|    110 |              116 |   5 |
|    140 |              355 |   6 |
+--------+------------------+-----+

我只想得到具有最大“num”值的宿舍ID和学生容量行(但允许绑定,所以我不能将行限制为1)。所以在这种情况下,返回 [160, 400, 12] .

我该如何处理?我试过放 where num = max(num) 在代码之后,但那不起作用。我使用 rank 函数和限制秩=1可能会有所帮助,但我不确定应该在哪里实现它。

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/38425

1453 次点击

文章 [ 3 ] | 最新文章 5 年前

• 1 楼

Courtney Foster 6 年前

聚合函数(如max、min、ave等)属于查询的Select部分,而不是Where部分。此外,不能嵌套聚合函数,因此max(count*)将不起作用。但是,您可以嵌套选择,从而以这种方式嵌套聚合函数。下面的例子来自OMG小马在这里的一个类似的帖子: Can I do a max(count(*)) in SQL

SELECT MAX(y.num) FROM 
    (SELECT COUNT(*) AS num FROM TABLE x) y

对于像您这样简单和初学者级别的查询,我建议使用这种方法,因为它比较简单,但是Madhur的答案和您使用rank的直觉也非常有效。

• 2 楼

Sarath Chandra Sam 6 年前

如果您使用的是MySQLverison8,那么您可以使用 WITH 条款:

WITH
counts AS (select dorm_id,count(*) as num from Dorm)
select dormid, dorm_capacity,count(*) 
from Dorm 
having count(*)=(select max(num) from counts)

• 3 楼

Madhur Bhaiya 6 年前

首先,我认为您不必要地使用了子选择查询( 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

登录后回复