如何在MySQL中获取各日期分组的最高计数记录

你可以通过以下两种方式实现每个日期分组中取inv_count最高的记录：

方法一：使用窗口函数（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数，按日期分组后对inv_count降序排序，取每组的第一条记录：

WITH monthly_inv_stats AS (
    SELECT 
        DATE_FORMAT(o.created_at, '%Y-%m') AS date,
        oi.inventory->>'$.id' AS inv_id, -- 简化JSON提取语法，效果与JSON_EXTRACT一致
        COUNT(oi.inventory->>'$.id') AS inv_count
    FROM orders AS o
    INNER JOIN order_items AS oi ON oi.order_id = o.id
    WHERE 
        o.created_at >= (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) - INTERVAL 12 MONTH 
        AND o.created_at < (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) + INTERVAL 1 MONTH
    GROUP BY date, inv_id
)
SELECT date, inv_id, inv_count
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY date ORDER BY inv_count DESC) AS rn
    FROM monthly_inv_stats
) t
WHERE rn = 1
ORDER BY date DESC;

说明： #

1. 先用CTEmonthly_inv_stats生成原查询的统计结果，同时简化JSON提取语法让代码更简洁
2. 外层查询通过ROW_NUMBER()按date分组，每组内按inv_count降序分配行号，行号为1的就是该月计数最高的记录

方法二：子查询关联（兼容MySQL 5.x版本） #

先统计每个日期的最大inv_count，再关联原统计结果筛选匹配的记录：

SELECT t1.date, t1.inv_id, t1.inv_count
FROM (
    SELECT 
        DATE_FORMAT(o.created_at, '%Y-%m') AS date,
        oi.inventory->>'$.id' AS inv_id,
        COUNT(oi.inventory->>'$.id') AS inv_count
    FROM orders AS o
    INNER JOIN order_items AS oi ON oi.order_id = o.id
    WHERE 
        o.created_at >= (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) - INTERVAL 12 MONTH 
        AND o.created_at < (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) + INTERVAL 1 MONTH
    GROUP BY date, inv_id
) t1
INNER JOIN (
    SELECT date, MAX(inv_count) AS max_count
    FROM (
        SELECT 
            DATE_FORMAT(o.created_at, '%Y-%m') AS date,
            COUNT(oi.inventory->>'$.id') AS inv_count
        FROM orders AS o
        INNER JOIN order_items AS oi ON oi.order_id = o.id
        WHERE 
            o.created_at >= (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) - INTERVAL 12 MONTH 
            AND o.created_at < (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) + INTERVAL 1 MONTH
        GROUP BY date, inv_id
    ) t2
    GROUP BY date
) t3 ON t1.date = t3.date AND t1.inv_count = t3.max_count
ORDER BY t1.date DESC;

说明： #

1. 最内层子查询t2生成原统计结果，中间层t3统计每个日期的最大inv_count
2. 外层通过t1和t3关联，筛选出每个日期下计数等于最大值的记录

如果同一日期存在多个inv_id的inv_count相同且都是最大值，两种方法都会返回所有符合条件的记录；如果只想返回其中一条，方法一中可以在ORDER BY后追加inv_id排序来固定结果。

内容的提问来源于stack exchange，提问作者Evan Mahendra