如何通过SQL批量获取指定用户符合条件的最新支付记录？

我需要批量获取一组用户ID的最新支付记录，但一直无法通过统一查询实现。

最初使用的SQL查询：

SELECT t1.*
FROM movements t1 LEFT JOIN movements t2
ON (t1.user = t2.user AND t1.id < t2.id)
WHERE t2.id IS NULL
AND t1.user IN ({$ids}) AND t1.type='payment' AND t1.concept!='4' AND t1.confirmed

该查询有一定效果，但部分记录被遗漏。调整条件为：

ON (t1.user = t2.user AND t1.id < t2.id)
WHERE t2.id IS NULL AND t2.date IS NULL

结果有所增加，但仍有部分记录未被选中。以下是两个查询无结果的示例数据：

id      user    concept             date                    type        confirmed
---------------------------------------------------------------------------------
29755   107     3                   2022-06-12 00:01:00     payment     1
31257   107     3                   2022-07-12 00:00:00     payment     1
32189   107     3                   2022-08-12 00:00:00     payment     1         
32460   107     COMISSION BALANCE   2022-08-23 10:50:50     comission   

id      user    concept             date                    type        confirmed
---------------------------------------------------------------------------------
27298   8408    3                   2022-03-11 08:44:53 40  payment     
28446   8408    3                   2022-03-11 00:01:00 40  payment     1         
28447   8408    3                   2022-04-19 17:22:42 40  payment

而针对单个用户的查询能得到正确结果：

SELECT * FROM movements WHERE user=107 AND type='payment' AND concept!='4' AND confirmed ORDER BY id DESC LIMIT 1

返回结果：

id      user    concept             date                    type        confirmed
---------------------------------------------------------------------------------
32189   107     3                   2022-08-12 00:00:00     payment     1

我可以通过foreach循环对每个用户单独调用查询，但希望避免这种方式。问题出在“最顶部”的行不符合完整条件，但不知道如何让SQL筛选出符合要求的记录，请问如何简洁实现这一需求？

原查询问题分析 #

你原来的左连接方法失效的原因是：当用户存在非支付类型的更大ID记录时（比如第一个示例里的comission类型记录），t2会匹配到这条记录，导致t2.id不为空，从而过滤掉了用户最新的支付记录。原查询没有限定t2的记录类型，错误地把所有更大ID的记录都纳入了判断范围。

方法1：使用窗口函数（推荐） #

利用ROW_NUMBER()窗口函数按用户分组，对符合条件的支付记录按ID降序排序，取每组的第一条记录：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY id DESC) AS rn
    FROM movements
    WHERE 
        user IN ({$ids}) 
        AND type = 'payment' 
        AND concept != '4' 
        AND confirmed = 1
) t
WHERE rn = 1;

• 内层查询先筛选出所有符合条件的支付记录；
• PARTITION BY user按用户分组，ORDER BY id DESC确保每组内最新的记录排在最前面；
• 外层查询取rn=1的记录，即每个用户最新的支付记录。

方法2：子查询获取每个用户的最大符合条件ID #

先统计每个用户符合条件的最大支付记录ID，再关联原表获取完整数据：

SELECT m.*
FROM movements m
JOIN (
    SELECT user, MAX(id) AS max_id
    FROM movements
    WHERE 
        user IN ({$ids}) 
        AND type = 'payment' 
        AND concept != '4' 
        AND confirmed = 1
    GROUP BY user
) t ON m.user = t.user AND m.id = t.max_id;

• 子查询先找出每个用户符合条件的支付记录中的最大ID；
• 主查询通过用户ID和最大ID关联，获取对应的完整记录。

这两种方法都能高效批量获取目标数据，避免循环查询的性能问题。

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