如何在SQL中替换非数字ID行并合并其价格为单行？

需求说明 #

对customers表执行以下处理：

• 若id为非数字值，将其替换为'0'，zip替换为'000'，并把所有这类行的price求和合并为一行
• 数字id的行保持原数据不变

原表数据 #

期望结果 #

SQL实现 #

MySQL 版本

通过REGEXP判断id是否为纯数字，结合UNION ALL拆分处理两类数据：

-- 保留数字ID的原始行
SELECT id, zip, price
FROM customers
WHERE id REGEXP '^[0-9]+$'

UNION ALL

-- 合并非数字ID的行并汇总价格
SELECT '0' AS id, '000' AS zip, SUM(price) AS price
FROM customers
WHERE NOT id REGEXP '^[0-9]+$';

PostgreSQL 版本

使用~正则操作符实现数字判断，逻辑与MySQL一致：

-- 保留数字ID的原始行
SELECT id, zip, price
FROM customers
WHERE id ~ '^[0-9]+$'

UNION ALL

-- 合并非数字ID的行并汇总价格
SELECT '0'::text AS id, '000'::text AS zip, SUM(price) AS price
FROM customers
WHERE NOT id ~ '^[0-9]+$';

SQL Server 版本

借助ISNUMERIC()函数识别数字ID（若需严格匹配纯数字，可结合正则进一步过滤）：

-- 保留数字ID的原始行
SELECT id, zip, price
FROM customers
WHERE ISNUMERIC(id) = 1

UNION ALL

-- 合并非数字ID的行并汇总价格
SELECT '0' AS id, '000' AS zip, SUM(price) AS price
FROM customers
WHERE ISNUMERIC(id) = 0;

逻辑解释 #

• 第一部分查询筛选出所有id为纯数字的行，直接返回原数据
• 第二部分查询筛选出非数字id的行，统一替换指定字段后对price求和
• 用UNION ALL将两部分结果合并，得到最终符合要求的表结构

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