Oracle表间数据同步：请求从表A插入/更新表B的SQL查询语句

嘿，这就帮你搞定把表A中逗号分隔的地区拆分成表B单条记录的SQL需求～

需求回顾 #

我们需要将表A中用逗号分隔的地区字段拆分为单独的行，同步到表B，让表B的每条记录对应一个独立的地区，最终达到你给出的目标状态。

全量同步方案（清空表B后插入所有拆分数据） #

下面针对不同主流数据库给出具体SQL：

MySQL 版本 #

MySQL没有内置的字符串拆分函数，我们可以用递归CTE来实现拆分：

-- 先清空表B（如果需要完全替换现有数据）
TRUNCATE TABLE 表B;

-- 递归拆分地区并插入到表B
WITH RECURSIVE split_regions AS (
    -- 初始行：取第一个地区和剩余的地区字符串
    SELECT 
        类别,
        名称,
        SUBSTRING_INDEX(地区, ',', 1) AS 地区,
        SUBSTRING(地区, LENGTH(SUBSTRING_INDEX(地区, ',', 1)) + 2) AS remaining_regions
    FROM 表A
    WHERE 地区 IS NOT NULL AND 地区 != ''
    
    UNION ALL
    
    -- 递归处理剩余的地区字符串
    SELECT 
        类别,
        名称,
        SUBSTRING_INDEX(remaining_regions, ',', 1) AS 地区,
        SUBSTRING(remaining_regions, LENGTH(SUBSTRING_INDEX(remaining_regions, ',', 1)) + 2) AS remaining_regions
    FROM split_regions
    WHERE remaining_regions IS NOT NULL AND remaining_regions != ''
)
INSERT INTO 表B (类别, 名称, 地区)
SELECT 类别, 名称, TRIM(地区) AS 地区  -- 用TRIM去除地区前后的空格
FROM split_regions;

SQL Server 版本 #

SQL Server有内置的STRING_SPLIT函数，实现起来更简单：

-- 清空表B（全量同步时使用）
TRUNCATE TABLE 表B;

-- 拆分地区并插入
INSERT INTO 表B (类别, 名称, 地区)
SELECT 
    a.类别,
    a.名称,
    TRIM(s.value) AS 地区  -- 处理地区前后的空格
FROM 表A a
CROSS APPLY STRING_SPLIT(a.地区, ',') s
WHERE s.value IS NOT NULL AND s.value != '';

PostgreSQL 版本 #

PostgreSQL可以用STRING_TO_ARRAY结合UNNEST来拆分字符串：

-- 清空表B（全量同步时使用）
TRUNCATE TABLE 表B;

-- 拆分地区并插入
INSERT INTO 表B (类别, 名称, 地区)
SELECT 
    类别,
    名称,
    TRIM(unnest(string_to_array(地区, ','))) AS 地区  -- 拆分并去除空格
FROM 表A
WHERE 地区 IS NOT NULL AND 地区 != '';

增量同步方案（仅更新/新增变化的数据） #

如果不需要清空表B，而是要增量同步（比如表A有更新时，同步对应的拆分记录），可以用以下方案（以MySQL为例，其他数据库可类似调整）：
首先需要给表B创建唯一组合键：ALTER TABLE 表B ADD UNIQUE KEY idx_category_name_region (类别, 名称, 地区);

然后用ON DUPLICATE KEY UPDATE来处理：

WITH RECURSIVE split_regions AS (
    -- 同全量方案的递归拆分逻辑
    SELECT 
        类别,
        名称,
        SUBSTRING_INDEX(地区, ',', 1) AS 地区,
        SUBSTRING(地区, LENGTH(SUBSTRING_INDEX(地区, ',', 1)) + 2) AS remaining_regions
    FROM 表A
    WHERE 地区 IS NOT NULL AND 地区 != ''
    
    UNION ALL
    
    SELECT 
        类别,
        名称,
        SUBSTRING_INDEX(remaining_regions, ',', 1) AS 地区,
        SUBSTRING(remaining_regions, LENGTH(SUBSTRING_INDEX(remaining_regions, ',', 1)) + 2) AS remaining_regions
    FROM split_regions
    WHERE remaining_regions IS NOT NULL AND remaining_regions != ''
)
INSERT INTO 表B (类别, 名称, 地区)
SELECT 类别, 名称, TRIM(地区) AS 地区
FROM split_regions
ON DUPLICATE KEY UPDATE 
    -- 如果表B有其他需要更新的字段（比如更新时间），可以在这里添加
    updated_at = CURRENT_TIMESTAMP;

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