MySQL 5按recurrence number及最新日期分组查询求助

安全阿岳

2026-7-6

问题：按分组获取每组最新日期的完整记录（MySQL 5）

原始查询及结果

以下查询返回符合条件的所有延迟记录：

SELECT 
    r.rec_num as Column_A
    ,c.amount as Column_B
    ,c.status as Column_C
    ,c.currency_code_to as Column_D
    ,c.due_date as Column_E
    ,c.id_recurrency as Column_F
FROM purchase c
JOIN recurrency r ON c.id_recurrency = r.id
JOIN item_product ip ON c.item_estoque = ip.id
JOIN signature a ON a.id = ip.signature
WHERE a.id = :id
    AND r.plano_signature IS NOT NULL
    AND r.rec_num IN (1,2,3,4)
    AND r.rec_num NOT IN (
        SELECT rn.recurrence
        FROM negotiate n
        JOIN negotiate_recurrence rn ON rn.negotiate = n.id
        JOIN purchase purchase ON purchase.id = n.purchase
        WHERE n.subscription = a.id
            AND rn.recurrence = r.rec_num
            AND purchase.status IN ('VALID','FILED')
        )
    AND r.rec_num NOT IN (
        SELECT r2.rec_num
        FROM purchase c2
        JOIN recurrency r2 ON c2.id_recurrency = r2.id
        JOIN item_product ip2 ON c2.item_estoque = ip2.id
        WHERE ip2.signature = a.id
            AND c2.status IN ('VALID','FILED')
        )
    AND c.id NOT IN (
        SELECT n1.purchase
        FROM negotiate n1
        WHERE n1.subscription = a.id
        )
    AND c.status = 'DELAYED'

查询结果：

Column A	Column B	Column C	Column D	Column E	Column F
2	1000	DELAYED	BRL	2023-10-04T19:48:17.000Z	11102501
2	250	DELAYED	BRL	2023-10-04T19:54:16.000Z	11102502
2	250	DELAYED	BRL	2023-10-09T23:30:20.000Z	11102503
2	250	DELAYED	BRL	2023-10-14T18:14:20.000Z	12042504
3	250	DELAYED	BRL	2023-11-04T12:51:33.000Z	13842790

期望结果

需要按Column A（rec_num）分组，返回每组中Column E（due_date）最新的完整记录：

Column A	Column B	Column C	Column D	Column E	Column F
2	250	DELAYED	BRL	2023-10-14T18:14:20.000Z	12042504
3	250	DELAYED	BRL	2023-11-04T12:51:33.000Z	13842790

尝试的错误方法及结果

尝试用MAX(due_date)结合GROUP BY rec_num，但得到的结果不符合预期：

SELECT 
    r.rec_num as Column_A
    ,c.amount as Column_B
    ,c.status as Column_C
    ,c.currency_code_to as Column_D
    ,max(c.due_date) as Column_E
    ,c.id_recurrency as Column_F
FROM purchase c
JOIN recurrency r ON c.id_recurrency = r.id
JOIN item_product ip ON c.item_estoque = ip.id
JOIN signature a ON a.id = ip.signature
WHERE a.id = :id
    AND r.plano_signature IS NOT NULL
    AND r.rec_num IN (1,2,3,4)
    AND r.rec_num NOT IN (
        SELECT rn.recurrence
        FROM negotiate n
        JOIN negotiate_recurrence rn ON rn.negotiate = n.id
        JOIN purchase purchase ON purchase.id = n.purchase
        WHERE n.subscription = a.id
            AND rn.recurrence = r.rec_num
            AND purchase.status IN ('VALID','FILED')
        )
    AND r.rec_num NOT IN (
        SELECT r2.rec_num
        FROM purchase c2
        JOIN recurrency r2 ON c2.id_recurrency = r2.id
        JOIN item_product ip2 ON c2.item_estoque = ip2.id
        WHERE ip2.signature = a.id
            AND c2.status IN ('VALID','FILED')
        )
    AND c.id NOT IN (
        SELECT n1.purchase
        FROM negotiate n1
        WHERE n1.subscription = a.id
        )
GROUP BY r.rec_num

错误结果：

Column A	Column B	Column C	Column D	Column E	Column F
2	1000	DELAYED	BRL	2023-10-14T18:14:20.000Z	11102501
3	250	DELAYED	BRL	2023-11-04T12:51:33.000Z	13842790

解决方案

问题原因

MySQL 5中，GROUP BY仅指定rec_num时，未被包含在分组中的列（如amount、id_recurrency）会随机选取分组内某一行的值，而非对应MAX(due_date)的那一行，导致结果错误。

正确查询（关联子查询方式）

由于MySQL 5不支持窗口函数（如ROW_NUMBER()），可以通过关联子查询找到每个rec_num对应的最大due_date，再筛选出该行的完整记录：

SELECT 
    r.rec_num as Column_A
    ,c.amount as Column_B
    ,c.status as Column_C
    ,c.currency_code_to as Column_D
    ,c.due_date as Column_E
    ,c.id_recurrency as Column_F
FROM purchase c
JOIN recurrency r ON c.id_recurrency = r.id
JOIN item_product ip ON c.item_estoque = ip.id
JOIN signature a ON a.id = ip.signature
WHERE a.id = :id
    AND r.plano_signature IS NOT NULL
    AND r.rec_num IN (1,2,3,4)
    AND r.rec_num NOT IN (
        SELECT rn.recurrence
        FROM negotiate n
        JOIN negotiate_recurrence rn ON rn.negotiate = n.id
        JOIN purchase purchase ON purchase.id = n.purchase
        WHERE n.subscription = a.id
            AND rn.recurrence = r.rec_num
            AND purchase.status IN ('VALID','FILED')
    )
    AND r.rec_num NOT IN (
        SELECT r2.rec_num
        FROM purchase c2
        JOIN recurrency r2 ON c2.id_recurrency = r2.id
        JOIN item_product ip2 ON c2.item_estoque = ip2.id
        WHERE ip2.signature = a.id
            AND c2.status IN ('VALID','FILED')
    )
    AND c.id NOT IN (
        SELECT n1.purchase
        FROM negotiate n1
        WHERE n1.subscription = a.id
    )
    AND c.status = 'DELAYED'
    -- 核心条件：匹配当前rec_num下的最大due_date
    AND c.due_date = (
        SELECT MAX(c2.due_date)
        FROM purchase c2
        JOIN recurrency r2 ON c2.id_recurrency = r2.id
        JOIN item_product ip2 ON c2.item_estoque = ip2.id
        JOIN signature a2 ON a2.id = ip2.signature
        WHERE a2.id = :id
            AND r2.rec_num = r.rec_num
            AND r2.plano_signature IS NOT NULL
            AND r2.rec_num IN (1,2,3,4)
            AND r2.rec_num NOT IN (
                SELECT rn.recurrence
                FROM negotiate n
                JOIN negotiate_recurrence rn ON rn.negotiate = n.id
                JOIN purchase purchase ON purchase.id = n.purchase
                WHERE n.subscription = a2.id
                    AND rn.recurrence = r2.rec_num
                    AND purchase.status IN ('VALID','FILED')
            )
            AND r2.rec_num NOT IN (
                SELECT r3.rec_num
                FROM purchase c3
                JOIN recurrency r3 ON c3.id_recurrency = r3.id
                JOIN item_product ip3 ON c3.item_estoque = ip3.id
                WHERE ip3.signature = a2.id
                    AND c3.status IN ('VALID','FILED')
            )
            AND c2.id NOT IN (
                SELECT n1.purchase
                FROM negotiate n1
                WHERE n1.subscription = a2.id
            )
            AND c2.status = 'DELAYED'
    );

简化版（复用原始查询作为子查询）

如果不想重复写条件，也可以把原始查询封装为子查询，再关联获取分组最大日期的结果：

SELECT t.*
FROM (
    -- 原始查询
    SELECT 
        r.rec_num as Column_A
        ,c.amount as Column_B
        ,c.status as Column_C
        ,c.currency_code_to as Column_D
        ,c.due_date as Column_E
        ,c.id_recurrency as Column_F
    FROM purchase c
    JOIN recurrency r ON c.id_recurrency = r.id
    JOIN item_product ip ON c.item_estoque = ip.id
    JOIN signature a ON a.id = ip.signature
    WHERE a.id = :id
        AND r.plano_signature IS NOT NULL
        AND r.rec_num IN (1,2,3,4)
        AND r.rec_num NOT IN (
            SELECT rn.recurrence
            FROM negotiate n
            JOIN negotiate_recurrence rn ON rn.negotiate = n.id
            JOIN purchase purchase ON purchase.id = n.purchase
            WHERE n.subscription = a.id
                AND rn.recurrence = r.rec_num
                AND purchase.status IN ('VALID','FILED')
        )
        AND r.rec_num NOT IN (
            SELECT r2.rec_num
            FROM purchase c2
            JOIN recurrency r2 ON c2.id_recurrency = r2.id
            JOIN item_product ip2 ON c2.item_estoque = ip2.id
            WHERE ip2.signature = a.id
                AND c2.status IN ('VALID','FILED')
        )
        AND c.id NOT IN (
            SELECT n1.purchase
            FROM negotiate n1
            WHERE n1.subscription = a.id
        )
        AND c.status = 'DELAYED'
) t
INNER JOIN (
    -- 获取每个rec_num的最大due_date
    SELECT Column_A, MAX(Column_E) AS max_due_date
    FROM (
        SELECT 
            r.rec_num as Column_A
            ,c.due_date as Column_E
        FROM purchase c
        JOIN recurrency r ON c.id_recurrency = r.id
        JOIN item_product ip ON c.item_estoque = ip.id
        JOIN signature a ON a.id = ip.signature
        WHERE a.id = :id
            AND r.plano_signature IS NOT NULL
            AND r.rec_num IN (1,2,3,4)
            AND r.rec_num NOT IN (
                SELECT rn.recurrence
                FROM negotiate n
                JOIN negotiate_recurrence rn ON rn.negotiate = n.id
                JOIN purchase purchase ON purchase.id = n.purchase
                WHERE n.subscription = a.id
                    AND rn.recurrence = r.rec_num
                    AND purchase.status IN ('VALID','FILED')
            )
            AND r.rec_num NOT IN (
                SELECT r2.rec_num
                FROM purchase c2
                JOIN recurrency r2 ON c2.id_recurrency = r2.id
                JOIN item_product ip2 ON c2.item_estoque = ip2.id
                WHERE ip2.signature = a.id
                    AND c2.status IN ('VALID','FILED')
            )
            AND c.id NOT IN (
                SELECT n1.purchase
                FROM negotiate n1
                WHERE n1.subscription = a.id
            )
            AND c.status = 'DELAYED'
    ) t2
    GROUP BY Column_A
) t3 ON t.Column_A = t3.Column_A AND t.Column_E = t3.max_due_date;

内容的提问来源于stack exchange，提问作者Patrick de Souza Teixeira

MySQL 5按recurrence number及最新日期分组查询求助

原始查询及结果 #

期望结果 #

尝试的错误方法及结果 #

解决方案 #

问题原因 #

正确查询（关联子查询方式） #

简化版（复用原始查询作为子查询） #