带count和maximum函数的查询

7jmck4yq  于 2021-07-26  发布在  Java
关注(0)|答案(3)|浏览(274)

我将此表作为以下查询的结果:

select id, count(*) C
from t1
group by id

ID.   C 
01.   11
02.   21
03.   18
04.   09
05.   21
...

如何获取与最大计数关联的所有ID在我的案例02和05中)
我试过:

select id, count(*) C
from t1
group by id
where c= max(c)

但它不起作用

iugsix8n

iugsix8n1#

如果可以将ID作为逗号分隔的列表处理,则可以使用:

select cnt, group_concat(id)
from (select id, count(*) as cnt
      from t1
      group by id
     ) i
order by cnt
limit 1;
kxe2p93d

kxe2p93d2#

在MySQL5.x中,我会引用一个子查询来获取您想要过滤的最大值。。。

select id, count(*) c
from t1
group by id
having c = (SELECT MAX(c) FROM (SELECT COUNT(*) c FROM t1 GROUP BY id) t1_agg)

或者。。。

select id, count(*) c
from t1
group by id
having c = (
  SELECT COUNT(*)
    FROM t1
GROUP BY id
ORDER BY COUNT(*) DESC
   LIMIT 1
)

在mysql 8中,我将使用一个窗口函数。。。

WITH
  t1_agg AS
(
  SELECT
    id,
    COUNT(*)   AS c,
    MAX(COUNT(*)) OVER ()   AS max_c
  FROM
    t1
  GROUP BY
    id
)
SELECT
  *
FROM
  t1_agg
WHERE
  c = max_c
kwvwclae

kwvwclae3#

您可以使用子查询,只需选择 id 符合 max(id) ,就像。。。

SELECT FirstName, Count(id) AS count
FROM Person
GROUP BY FirstName
HAVING count = (
    SELECT MAX(count1)
    FROM (
        SELECT FirstName, Count(id) AS count1
        FROM Person
        GROUP BY FirstName
    )
p2)

子查询是一个强大的工具,因为它是。。。
…另一条语句中的select语句(来源:dev.mysql.com。)

相关问题