MySQL按ID/REV统计最后RESET后物品收发差值的查询方案

当然可以用单条SQL搞定这个需求！我来帮你拆解思路并写出对应的查询语句，完全覆盖你要的9组结果~

核心需求回顾 #

• 仅统计每个ID最后一次RESET操作之后的记录
• 从content字段提取数值：delivered（交付）记为正，returned（退回）记为负
• 必须输出所有ID（ID1/ID2/ID3）与所有SYS类型（SYS1/SYS2/SYS3）的组合，哪怕某个组合没有有效记录，差值显示为0

完整SQL查询语句 #

WITH last_reset AS (
    -- 1. 获取每个ID的最后一次RESET操作时间，作为筛选记录的时间分界点
    SELECT id, MAX(tDate) AS last_reset_time
    FROM docs
    WHERE rev = 'RESET'
    GROUP BY id
),
valid_records AS (
    -- 2. 筛选出分界点后的有效业务记录，并计算每条记录的正负数值
    SELECT 
        d.id,
        d.rev,
        CASE 
            WHEN d.content LIKE '%delivered%' THEN CAST(REGEXP_SUBSTR(d.content, '[0-9]+') AS SIGNED)
            WHEN d.content LIKE '%returned%' THEN -CAST(REGEXP_SUBSTR(d.content, '[0-9]+') AS SIGNED)
            ELSE 0
        END AS value
    FROM docs d
    JOIN last_reset lr ON d.id = lr.id
    WHERE d.tDate > lr.last_reset_time
      AND d.rev IN ('SYS1', 'SYS2', 'SYS3') -- 排除RESET操作本身
),
all_combinations AS (
    -- 3. 生成所有需要的ID+REV组合，确保9组结果全部出现
    SELECT DISTINCT d.id, s.rev
    FROM docs d
    CROSS JOIN (
        SELECT 'SYS1' AS rev UNION ALL 
        SELECT 'SYS2' AS rev UNION ALL 
        SELECT 'SYS3' AS rev
    ) s
    WHERE d.id IN ('ID1', 'ID2', 'ID3')
)
-- 4. 关联组合表与有效记录，分组求和得到最终收发差值
SELECT 
    ac.id,
    ac.rev,
    COALESCE(SUM(vr.value), 0) AS balance
FROM all_combinations ac
LEFT JOIN valid_records vr ON ac.id = vr.id AND ac.rev = vr.rev
GROUP BY ac.id, ac.rev
ORDER BY ac.id, ac.rev;

语句分步解释 #

1. last_reset CTE：通过分组聚合，拿到每个ID最新的RESET时间，这是我们筛选记录的时间阈值，确保只统计重置后的操作。
2. valid_records CTE：关联时间阈值，只保留阈值之后的业务记录；用正则表达式提取content里的数字，根据关键词判断正负，把文本描述转成可计算的数值。
3. all_combinations CTE：通过笛卡尔积生成所有ID和SYS的组合，避免因为某组没有记录而缺失结果。
4. 最终查询：左连接组合表和有效记录表，用COALESCE把无记录的NULL转为0，最后按ID和REV分组求和，得到你需要的9组收发差值。

预期执行结果 #

执行后会得到如下9组完整结果：

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