MySQL 不带 Order By 的最大计数

发布于 2024-11-15 22:27:42 字数 336 浏览 1 评论 0原文

我有以下 MySQL 行：

SELECT Age, count(*) AS Total FROM pacient WHERE Age BETWEEN 20 AND 40 GROUP BY Age ORDER BY Age 我需要向其中添加一个仅显示的附加列每行的 count(*) 的最大值。例如：

13 2 7
18 2 7
23 5 7
24 7 7
26 6 7
32 3 7
38 1 7
41 1 7
46 4 7

这将是 3 列，第三列显示 7，因为 7 是进行 count(*) 的第二列中的最大数字。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谁的新欢旧爱 2024-11-22 22:27:42

这里是解决方案：

select age,
    count(*),
    (select max(c) from 
        (select count(*) as c from pacient where age between 20 and 40 group by age) as x) as t 
from pacient 
where age between 20 and 40 
group by age 
order by age;

Here the solution:

select age,
    count(*),
    (select max(c) from 
        (select count(*) as c from pacient where age between 20 and 40 group by age) as x) as t 
from pacient 
where age between 20 and 40 
group by age 
order by age;

回复收藏 0 原文

想你的星星会说话 2024-11-22 22:27:42

您是否尝试过用另一个查询包装您的查询？类似的东西

SELECT A.age, A.total, MAX(A.total) as max_value FROM (
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age) as A
GROUP BY A.age, A.total

Have you tried to wrap your query with another query? something like

SELECT A.age, A.total, MAX(A.total) as max_value FROM (
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age) as A
GROUP BY A.age, A.total

回复收藏 0 原文

静待花开 2024-11-22 22:27:42

select 
      p.Age,
      count(*)  CountPerAge,
      max(ar.AllRecs) AllRecs
   from
      pacient p,
      ( select count(*) AllRecs 
           from pacient p2
           where p2.age between 20 and 40 ) ar
   where 
      p.age between 20 and 40
   group by
      p.age

通过在没有连接条件的情况下对第二个“子选择”进行连接，它将给出笛卡尔结果...由于它是没有分组依据的计数，因此它将始终返回单个记录，从而连接到所有年龄行，否则。该值的 MAX() 没有问题，因为它是唯一的记录将按原样返回。

select 
      p.Age,
      count(*)  CountPerAge,
      max(ar.AllRecs) AllRecs
   from
      pacient p,
      ( select count(*) AllRecs 
           from pacient p2
           where p2.age between 20 and 40 ) ar
   where 
      p.age between 20 and 40
   group by
      p.age

By doing a join to the second "subselect" with no join condition, it will give a Cartesian result... Since it is a count with no group by, it will always return a single record and thus be joined to all age rows otherwise. The MAX() of the value is no problem since it is the only record will just be returned as-is.

回复收藏 0 原文

懷念過去 2024-11-22 22:27:42

使用 SQL 视图而不是使用子查询总是好的。因为 VIEW 将具有已经编译的结果。

CREATE VIEW subqueryView 
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age

SELECT A.age, A.total, MAX(A.total) as max_value FROM (SELECT FROM subqueryView) as A
GROUP BY A.age, A.total

It is always good to use SQL VIEWS instead of using sub queries. Because VIEW will be having already compiled result.

CREATE VIEW subqueryView 
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age

SELECT A.age, A.total, MAX(A.total) as max_value FROM (SELECT FROM subqueryView) as A
GROUP BY A.age, A.total

回复收藏 0 原文

~没有更多了~