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

Question

我有以下查询，它适用于任何给定月份，为我提供该月达到的最高计数并指定日期:

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

有没有一种方法可以编辑此查询，以便获得每个月的最高天数？此查询一次仅适用于一个月...

Answer 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
                  );