按ID/Rev汇总表格：交付与退回物品差值计算优化方案求助

嘿，我明白你之前用CASE WHEN枚举所有可能的退回数量有多麻烦——这确实不够灵活，尤其是当数字范围变大的时候！咱们换个更聪明的方式来解决这个按ID/SYS汇总净物品数的问题。

核心思路 #

不用硬编码枚举0-99的情况，咱们直接从content字段里提取数字，再根据内容是「交付」还是「退回」给数字加上对应的正负号，最后按ID和SYS分组求和就搞定了。

具体SQL实现 #

假设你的表结构大概是这样的（你可以根据实际表名/字段名调整）：

CREATE TABLE item_records (
    ID VARCHAR(20),
    Rev VARCHAR(10),
    SYS VARCHAR(20),
    content VARCHAR(100)
);

下面这个SQL语句就能实现你要的效果：

SELECT 
    ID,
    SYS,
    SUM(
        CASE 
            -- 如果是退回，数字取负
            WHEN content LIKE '%returned%' THEN -1 * CAST(REGEXP_SUBSTR(content, '[0-9]+') AS UNSIGNED)
            -- 如果是交付，数字取正
            WHEN content LIKE '%delivered%' THEN CAST(REGEXP_SUBSTR(content, '[0-9]+') AS UNSIGNED)
            -- 其他情况（如果有的话）记为0
            ELSE 0
        END
    ) AS net_items
FROM item_records
GROUP BY ID, SYS
ORDER BY ID, SYS;

这个方法的优势 #

• 无需枚举数字：不管content里的数字是1还是1000，正则表达式都能准确提取，不用再一个个写CASE WHEN匹配不同数字。
• 扩展性强：如果以后出现其他类型的记录（比如「调整x件」），只需要在CASE里加一个分支就行，不用修改大量代码。
• 维护成本低：代码更简洁，逻辑一目了然，后续排查问题也方便。

如果你的数据库不支持REGEXP_SUBSTR（比如老版本的SQL Server），可以用PATINDEX结合SUBSTRING来提取数字，思路是一样的：

-- SQL Server 版本示例
SELECT 
    ID,
    SYS,
    SUM(
        CASE 
            WHEN content LIKE '%returned%' THEN -1 * CAST(SUBSTRING(content, PATINDEX('%[0-9]%', content), PATINDEX('%[^0-9]%', SUBSTRING(content, PATINDEX('%[0-9]%', content), 100)) - 1) AS INT)
            WHEN content LIKE '%delivered%' THEN CAST(SUBSTRING(content, PATINDEX('%[0-9]%', content), PATINDEX('%[^0-9]%', SUBSTRING(content, PATINDEX('%[0-9]%', content), 100)) - 1) AS INT)
            ELSE 0
        END
    ) AS net_items
FROM item_records
GROUP BY ID, SYS
ORDER BY ID, SYS;

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