MySQL关联多记录交易表 查询所有用户最新余额的实现问题

原SQL问题排查 #

• 使用INNER JOIN会过滤掉没有交易记录的用户，无法覆盖全量用户查询需求
• 未对交易记录做分组筛选，会返回同一用户的多条历史交易数据，未匹配「取最新交易余额」的要求

兼容所有MySQL版本的实现方案 #

思路为先从交易表提取每个用户的最新交易ID，再关联获取对应余额，最后和用户表左连接，无交易用户余额补0：

SELECT 
    u.user_id,
    u.first_name,
    u.last_name,
    IFNULL(ut.balance, 0) AS balance
FROM users u
-- 左连接保证所有用户都被保留
LEFT JOIN (
    -- 分组获取每个用户对应的最新交易ID
    SELECT username, MAX(transaction_id) AS max_transaction_id
    FROM user_transactions
    GROUP BY username
) latest_t ON u.user_id = latest_t.username
-- 关联获取最新交易对应的余额
LEFT JOIN user_transactions ut ON latest_t.max_transaction_id = ut.transaction_id;

MySQL 8.0+ 优化实现（窗口函数） #

如果使用MySQL 8.0及以上版本，可通过ROW_NUMBER()窗口函数简化逻辑：

SELECT 
    u.user_id,
    u.first_name,
    u.last_name,
    IFNULL(ut_ranked.balance, 0) AS balance
FROM users u
LEFT JOIN (
    SELECT 
        username,
        balance,
        -- 按用户分组，交易ID倒序排序取第一条即为最新交易
        ROW_NUMBER() OVER (PARTITION BY username ORDER BY transaction_id DESC) AS rank_num
    FROM user_transactions
) ut_ranked ON u.user_id = ut_ranked.username AND ut_ranked.rank_num = 1;

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