MySQL查询如何获取分组统计结果中最新的3条记录？

要获取原查询结果的最后3条（即最新3个月份的统计记录），可参考以下两种实现方案：

方案1：嵌套子查询+排序分页（兼容所有MySQL版本） #

将现有查询作为内层子查询，新增数字类型的年、月字段用于排序，避免格式化后的月份字符串排序出错：

SELECT * FROM (
    SELECT icm_external_data.client_id,
                icm_external_data.gsheet_tab AS source,
                icm_external_data.type,
                icm_external_data.name AS lead_name,
                COUNT(DISTINCT icm_leads.email) AS on_month_leads,
                date_format(icm_leads.date_created,'%M') AS month,
                date_format(icm_leads.date_created,'%Y') AS YEAR,
                @running_total := @running_total + COUNT(DISTINCT icm_leads.email) AS cumulative_sum,
                -- 新增排序用字段，保证排序准确性
                year(icm_leads.date_created) AS sort_year,
                month(icm_leads.date_created) AS sort_month
            FROM icm_leads
            LEFT JOIN icm_external_data ON icm_external_data.gsheet_id = icm_leads.gsheet_id
            JOIN (SELECT @running_total := 0) r
        WHERE icm_external_data.id = '29'
        AND icm_leads.gsheet_id = 'xxx'
        AND (icm_leads.tab_name = 'xxx' OR icm_leads.tab_name = 'xxx')
        AND icm_leads.email NOT LIKE '%xxx%'
        AND (icm_leads.`type` = 'forms' OR icm_leads.`type` = 'calls')
    GROUP BY year(icm_leads.date_created),month(icm_leads.date_created)
) AS t
ORDER BY sort_year DESC, sort_month DESC
LIMIT 3;

如果需要和原查询一致，按时间从早到晚升序展示结果，可再嵌套一层排序：

SELECT client_id, source, type, lead_name, on_month_leads, month, YEAR, cumulative_sum
FROM (
    SELECT * FROM (
        SELECT icm_external_data.client_id,
                    icm_external_data.gsheet_tab AS source,
                    icm_external_data.type,
                    icm_external_data.name AS lead_name,
                    COUNT(DISTINCT icm_leads.email) AS on_month_leads,
                    date_format(icm_leads.date_created,'%M') AS month,
                    date_format(icm_leads.date_created,'%Y') AS YEAR,
                    @running_total := @running_total + COUNT(DISTINCT icm_leads.email) AS cumulative_sum,
                    year(icm_leads.date_created) AS sort_year,
                    month(icm_leads.date_created) AS sort_month
                FROM icm_leads
                LEFT JOIN icm_external_data ON icm_external_data.gsheet_id = icm_leads.gsheet_id
                JOIN (SELECT @running_total := 0) r
            WHERE icm_external_data.id = '29'
            AND icm_leads.gsheet_id = 'xxx'
            AND (icm_leads.tab_name = 'xxx' OR icm_leads.tab_name = 'xxx')
            AND icm_leads.email NOT LIKE '%xxx%'
            AND (icm_leads.`type` = 'forms' OR icm_leads.`type` = 'calls')
        GROUP BY year(icm_leads.date_created),month(icm_leads.date_created)
    ) AS t1
    ORDER BY sort_year DESC, sort_month DESC
    LIMIT 3
) AS t2
ORDER BY sort_year ASC, sort_month ASC;

方案2：窗口函数实现（仅支持MySQL 8.0及以上版本） #

无需使用用户变量，兼容性和稳定性更好：

WITH monthly_stats AS (
    SELECT 
        icm_external_data.client_id,
        icm_external_data.gsheet_tab AS source,
        icm_external_data.type,
        icm_external_data.name AS lead_name,
        COUNT(DISTINCT icm_leads.email) AS on_month_leads,
        date_format(icm_leads.date_created,'%M') AS month,
        date_format(icm_leads.date_created,'%Y') AS YEAR,
        SUM(COUNT(DISTINCT icm_leads.email)) OVER (ORDER BY year(icm_leads.date_created), month(icm_leads.date_created)) AS cumulative_sum,
        year(icm_leads.date_created) AS sort_year,
        month(icm_leads.date_created) AS sort_month
    FROM icm_leads
    LEFT JOIN icm_external_data ON icm_external_data.gsheet_id = icm_leads.gsheet_id
    WHERE icm_external_data.id = '29'
    AND icm_leads.gsheet_id = 'xxx'
    AND (icm_leads.tab_name = 'xxx' OR icm_leads.tab_name = 'xxx')
    AND icm_leads.email NOT LIKE '%xxx%'
    AND (icm_leads.`type` = 'forms' OR icm_leads.`type` = 'calls')
    GROUP BY year(icm_leads.date_created),month(icm_leads.date_created)
)
-- 倒序返回最新3条
SELECT client_id, source, type, lead_name, on_month_leads, month, YEAR, cumulative_sum
FROM monthly_stats
ORDER BY sort_year DESC, sort_month DESC
LIMIT 3;

-- 如需升序展示，替换上面的查询语句为以下内容：
/*
SELECT client_id, source, type, lead_name, on_month_leads, month, YEAR, cumulative_sum
FROM (
    SELECT * FROM monthly_stats
    ORDER BY sort_year DESC, sort_month DESC
    LIMIT 3
) t
ORDER BY sort_year ASC, sort_month ASC;
*/

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