如何在MySQL分组查询中获取上月counterMAX值？

方法一：使用窗口函数 LAG()（MySQL 8.0+ 适用） #

这是最简洁高效的实现方式，利用LAG()窗口函数直接在分组统计结果中，按tag分组、时间顺序获取上月的counterMAX值：

WITH monthly_stats AS (
    SELECT 
        CONCAT(YEAR(timestamp), "-", LPAD(MONTH(timestamp), 2, '0')) AS DATE,
        MIN(counter) AS counterMIN,
        MAX(counter) AS counterMAX,
        MIN(timestamp) AS timestampMIN,
        MAX(timestamp) AS timestampMAX,
        tag
    FROM `seller` 
    WHERE tag LIKE "SYS%"
    GROUP BY YEAR(timestamp), MONTH(timestamp), tag
)
SELECT 
    DATE,
    tag,
    counterMIN,
    counterMAX,
    IFNULL(LAG(counterMAX) OVER (PARTITION BY tag ORDER BY DATE), 0) AS counterMAXPrevMonth
FROM monthly_stats
ORDER BY DATE, tag;

说明： #

1. 先用CTEmonthly_stats完成原有按月分组统计，将GROUP BY中的字符串拼接拆分为YEAR(timestamp), MONTH(timestamp)，逻辑更清晰且性能更优。
2. 外层查询通过LAG(counterMAX) OVER (PARTITION BY tag ORDER BY DATE)，实现按tag分组、时间排序，获取当前行上一行的counterMAX值。
3. 用IFNULL()将无上月数据的行（如最早月份）的counterMAXPrevMonth设为0，匹配需求。

方法二：自连接查询（兼容MySQL 5.x版本） #

若你的MySQL版本不支持窗口函数，可通过自连接关联分组后的表，匹配相同tag且月份为上月的记录：

SELECT 
    curr.DATE,
    curr.tag,
    curr.counterMIN,
    curr.counterMAX,
    IFNULL(prev.counterMAX, 0) AS counterMAXPrevMonth
FROM (
    SELECT 
        CONCAT(YEAR(timestamp), "-", LPAD(MONTH(timestamp), 2, '0')) AS DATE,
        MIN(counter) AS counterMIN,
        MAX(counter) AS counterMAX,
        tag
    FROM `seller` 
    WHERE tag LIKE "SYS%"
    GROUP BY YEAR(timestamp), MONTH(timestamp), tag
) curr
LEFT JOIN (
    SELECT 
        CONCAT(YEAR(timestamp), "-", LPAD(MONTH(timestamp), 2, '0')) AS DATE,
        MAX(counter) AS counterMAX,
        tag
    FROM `seller` 
    WHERE tag LIKE "SYS%"
    GROUP BY YEAR(timestamp), MONTH(timestamp), tag
) prev ON curr.tag = prev.tag 
    AND STR_TO_DATE(curr.DATE, '%Y-%m') = DATE_ADD(STR_TO_DATE(prev.DATE, '%Y-%m'), INTERVAL 1 MONTH)
ORDER BY curr.DATE, curr.tag;

说明： #

1. 子查询curr和prev分别获取当前月份及所有月份的统计数据。
2. 自连接条件为：tag相同，且当前月份日期是上月日期加1个月，通过STR_TO_DATE()转换字符串日期为日期类型，再用DATE_ADD()计算月份，避免字符串匹配误差。
3. 同样用IFNULL()处理无上月数据的场景。

测试结果 #

用你提供的测试数据执行上述查询，会得到如下符合需求的结果（以窗口函数方法为例）：

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