SQL如何将table1指定行转列迁移至table2并删除原表对应数据

核心逻辑 #

通过行转列聚合查询一次性把table1中指定的多行数据拼接为单行，插入table2后批量删除对应源数据，无需逐行操作，兼容绝大多数主流SQL数据库。

代码实现 #

1. 插入数据到目标表 #

以下写法用通用聚合语法实现行转列，不需要依赖数据库特有的PIVOT语法：

INSERT INTO table2 (n1, n2, n3)
SELECT
  MAX(CASE WHEN name = 'name1' THEN value END) AS n1,
  MAX(CASE WHEN name = 'name2' THEN value END) AS n2,
  MAX(CASE WHEN name = 'name3' THEN value END) AS n3
FROM table1
WHERE name IN ('name1', 'name2', 'name3');

提示：如果table1的name字段是唯一约束，上述聚合逻辑完全符合预期；如果存在重复name的记录，可以根据业务需要替换MAX为其他聚合函数（比如MIN、字符串拼接函数等）。

2. 删除源表已迁移数据 #

迁移完成后执行以下语句删除table1中对应的行：

DELETE FROM table1
WHERE name IN ('name1', 'name2', 'name3');

事务保障（可选） #

如果需要保证插入和删除操作的原子性，避免中间出错导致数据不一致，可以用事务包裹两个操作，以下是MySQL示例：

START TRANSACTION;
-- 插入逻辑
INSERT INTO table2 (n1, n2, n3)
SELECT
  MAX(CASE WHEN name = 'name1' THEN value END) AS n1,
  MAX(CASE WHEN name = 'name2' THEN value END) AS n2,
  MAX(CASE WHEN name = 'name3' THEN value END) AS n3
FROM table1
WHERE name IN ('name1', 'name2', 'name3');
-- 删除逻辑
DELETE FROM table1
WHERE name IN ('name1', 'name2', 'name3');
-- 操作都执行成功就提交，出错就执行ROLLBACK回滚
COMMIT;

如果是PostgreSQL可以用BEGIN替换START TRANSACTION，SQL Server用BEGIN TRANSACTION即可，核心逻辑一致。

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