MySQL 5.7如何获取同商户上一行的recent_point作为previous_point

现有表结构与数据 #

id    created_date   merchant_id   point_value   recent_point   status
 1    2022-01-02           1           5000           5000        earn 
 2    2022-01-02           2           3000           3000        earn
 3    2022-01-02           1           3000           3000        redeem

期望输出 #

id    created_date  merchant_id   status   previous_point   point_value   recent_point   
 1    2022-01-02         1         in           0             5000           5000        
 2    2022-01-02         1         out         5000           3000           2000        
 3    2022-01-02         2         in          0              3000           3000  

错误查询分析 #

你当前的查询通过adddate(mpl2.created_date, 1)关联表，逻辑完全偏离需求：我们需要的是同商户的上一条操作记录，而非前一天的记录，且同商户的多条操作可能发生在同一天，这种关联方式无法匹配到正确的前置记录。

正确查询语句 #

方法一：自关联子查询（适合小数据量） #

通过子查询找到当前记录同商户且ID更小的最近一条记录，获取其积分值：

SELECT
    mpl.id,
    mpl.created_date,
    mpl.merchant_id,
    CASE mpl.status 
        WHEN 'earn' THEN 'in' 
        WHEN 'redeem' THEN 'out' 
    END AS status,
    COALESCE(prev.recent_point, 0) AS previous_point,
    mpl.point_value,
    -- 若原表recent_point字段不准确，可替换为计算后的积分值：
    -- CASE mpl.status 
    --     WHEN 'earn' THEN COALESCE(prev.recent_point, 0) + mpl.point_value
    --     WHEN 'redeem' THEN COALESCE(prev.recent_point, 0) - mpl.point_value
    -- END AS recent_point,
    mpl.recent_point
FROM merchant_point_log mpl
LEFT JOIN merchant_point_log prev 
    ON mpl.merchant_id = prev.merchant_id 
    AND prev.id = (
        SELECT MAX(id) 
        FROM merchant_point_log 
        WHERE merchant_id = mpl.merchant_id 
        AND id < mpl.id
    )
ORDER BY mpl.id;

方法二：用户变量实现（适合大数据量，性能更优） #

MySQL 5.7不支持窗口函数，可通过用户变量跟踪当前商户和上一积分值，逐行计算：

SELECT
    id,
    created_date,
    merchant_id,
    status,
    previous_point,
    point_value,
    recent_point
FROM (
    SELECT
        mpl.id,
        mpl.created_date,
        mpl.merchant_id,
        CASE mpl.status WHEN 'earn' THEN 'in' WHEN 'redeem' THEN 'out' END AS status,
        mpl.point_value,
        -- 获取上一积分值，新商户则为0
        @prev_point := CASE 
            WHEN @current_merchant = mpl.merchant_id THEN @prev_point 
            ELSE 0 
        END AS previous_point,
        -- 若原表recent_point不准确，替换为计算后的积分值：
        -- CASE mpl.status 
        --     WHEN 'earn' THEN @prev_point + mpl.point_value
        --     WHEN 'redeem' THEN @prev_point - mpl.point_value
        -- END AS recent_point,
        mpl.recent_point,
        -- 更新变量，准备下一行计算
        @prev_point := CASE 
            WHEN @current_merchant = mpl.merchant_id THEN mpl.recent_point 
            ELSE mpl.recent_point 
        END,
        @current_merchant := mpl.merchant_id
    FROM merchant_point_log mpl
    -- 初始化变量
    CROSS JOIN (SELECT @current_merchant := -1, @prev_point := 0) vars
    -- 必须按商户+ID排序，保证计算顺序正确
    ORDER BY mpl.merchant_id, mpl.id
) t
ORDER BY id;

说明 #

• 若原表的recent_point字段值不准确（比如示例中ID=3的recent_point应为2000而非3000），可启用SQL中注释的计算逻辑，自动根据上一积分+/-当前操作值得到正确的当前积分。
• 两种方法均需保证id字段按操作顺序递增，否则需替换为created_date或其他可排序的时间字段。

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