MySQL如何查询所有用户的倒数第二次结账（checkout）记录？

数据表结构与数据 #

users_table #

id | name
-----------
1  | john
2  | thomas
3  | george

checkout_table #

id | cid | date
---------------
1  | 2   | 20240601
2  | 2   | 20240610
3  | 2   | 20240613
4  | 1   | 20240608
5  | 4   | 20240607
6  | 1   | 20240609

问题现状 #

单个用户的倒数第二次结账记录可通过以下SQL查询：

SELECT * 
FROM `checkout_table` AS c 
WHERE c.cid='2' 
ORDER BY c.date DESC 
LIMIT 1 OFFSET 1;

但尝试用LEFT JOIN批量查询时未得到预期结果：

SELECT u.*,c.cid as userId,c.date 
FROM `users_table` AS u 
LEFT JOIN `checkout_table` AS c ON c.cid=u.id 
GROUP BY c.cid 
ORDER BY u.id; 

查询结果（不符合需求）：

id name    userId  date    
 1  john    1       2024-06-08 17:52:00
 2  thomas  2       2024-06-01 09:00:00
 3  george  NULL    NULL 

需求是获取所有用户的倒数第二次结账记录，无记录或仅结账一次则对应字段返回null，期望结果：

id | name   | userid | date
----------------------------
1  | john   | 1      | 20240608
2  | thomas | 2      | 20240610
3  | george | null   | null

解决方案 #

使用**窗口函数ROW_NUMBER()**实现，具体SQL语句如下：

SELECT 
    u.id,
    u.name,
    sub.cid AS userid,
    sub.date
FROM users_table u
LEFT JOIN (
    SELECT 
        cid,
        date,
        ROW_NUMBER() OVER (PARTITION BY cid ORDER BY date DESC) AS rn
    FROM checkout_table
) sub ON u.id = sub.cid AND sub.rn = 2
ORDER BY u.id;

语句说明 #

• PARTITION BY cid：按用户ID分组，确保每个用户的结账记录单独排序
• ORDER BY date DESC：每组内按结账日期从新到旧排序，最新记录行号为1，倒数第二为2
• 子查询筛选rn=2的记录后，与用户表左连接，保证所有用户都出现在结果中，无符合条件记录时userid和date自动返回null

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