mysql - 需要一个SQL来获取全年的每月和每天的大量计数

我有以下查询，它将在任何给定的月份工作，为我提供在该月份达到的最高计数，并指定日期：

SELECT day(season),COUNT(*)
FROM baseball_season
WHERE season like '2017-07%'
GROUP BY day(season)
Order BY count(*) DESC
LIMIT 1;

是否有方法可以编辑此查询以获得每月的最高日数？这个查询一次只能处理一个月。。。

最佳答案

在MySQL中这相当痛苦，但有一种方法：

select year(season) as yyyy, month(season) as mm, day(season) as dd,
       count(*)
from baseball_season bs
where season >= '2017-01-01' and season < '2018-01-01'  -- or whatever, using dates
group by yyyy, mm, dd
having count(*) = (select count(*) as cnt
                   from baseball_season bs2
                   where year(bs2.season) = year(bs.season) and
                         month(bs2.season) = month(bs.season)
                   group by day(bs2.season)
                   order by count(*) desc
                   limit 1
                  );

关于mysql - 需要一个SQL来获取全年的每月和每天的大量计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45745270/