如何将数据表中多值字段拆分并展开为多行？

下面针对不同主流数据库，提供具体的实现方案：

MySQL（8.0+） #

MySQL 8.0及以上版本支持JSON_TABLE函数，可将拆分后的字符串转为行数据：

SELECT 
    t.error_id,
    j.role_id
FROM 
    your_table t
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(t.role_id, ';', '","'), '"]'),
    '$[*]' COLUMNS (role_id VARCHAR(10) PATH '$')
) j;

若使用MySQL 5.x版本（无JSON_TABLE），可借助数字辅助表实现：

-- 先创建数字辅助表（生成1-100的数字，可根据实际字段长度调整）
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (1),(2),(3),...,(100);

SELECT 
    t.error_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.role_id, ';', n.n), ';', -1) AS role_id
FROM 
    your_table t
JOIN numbers n 
    ON n.n <= LENGTH(t.role_id) - LENGTH(REPLACE(t.role_id, ';', '')) + 1
ORDER BY 
    t.error_id, n.n;

PostgreSQL #

PostgreSQL可直接用string_to_array配合unnest函数拆分：

SELECT 
    error_id,
    unnest(string_to_array(role_id, ';')) AS role_id
FROM 
    your_table;

SQL Server #

SQL Server 2016+支持STRING_SPLIT函数：

SELECT 
    t.error_id,
    s.value AS role_id
FROM 
    your_table t
CROSS APPLY STRING_SPLIT(t.role_id, ';') s;

若为SQL Server 2016之前版本，可使用递归CTE：

WITH cte AS (
    SELECT 
        error_id,
        role_id,
        CHARINDEX(';', role_id) AS split_pos
    FROM your_table
    UNION ALL
    SELECT 
        error_id,
        SUBSTRING(role_id, split_pos + 1, LEN(role_id)),
        CHARINDEX(';', SUBSTRING(role_id, split_pos + 1, LEN(role_id)))
    FROM cte
    WHERE split_pos > 0
)
SELECT 
    error_id,
    CASE WHEN split_pos > 0 THEN LEFT(role_id, split_pos - 1) ELSE role_id END AS role_id
FROM cte
ORDER BY error_id;

通用递归思路（适配多数支持CTE的数据库） #

1. 初始层：获取每条记录的error_id、完整role_id，定位第一个分号的位置
2. 迭代层：每次截取分号前的内容作为单独行，再处理剩余字符串
3. 终止条件：当剩余字符串无分号时，处理最后一段内容

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