如何将SELECT语句返回的单列合并值拆分为多行？

你的运单编号是固定18位格式（示例中1ZX925480306189770等均为18位），可以按固定长度拆分后转为多行，同时保留其他列。以下是主流数据库的实现方法：

1. SQL Server #

方法一：递归CTE #

WITH SplitCTE AS (
    SELECT 
        -- 保留其他列，替换为你的实际列名或用*代替
        *,
        1 AS StartPos,
        LEN(TRACKING_NUMBER) AS TotalLen
    FROM YourTableName
    WHERE TRACKING_NUMBER IS NOT NULL AND LEN(TRACKING_NUMBER) >= 18
    UNION ALL
    SELECT 
        *,
        StartPos + 18,
        TotalLen
    FROM SplitCTE
    WHERE StartPos + 18 <= TotalLen
)
SELECT 
    -- 列出需要保留的其他列
    Column1, Column2,
    SUBSTRING(TRACKING_NUMBER, StartPos, 18) AS TRACKING_NUMBER
FROM SplitCTE
ORDER BY Column1; -- 按主键排序保证顺序

方法二：数字表关联（更高效） #

如果有现成的数字表（存储1到N的整数），可以直接用：

SELECT 
    t.Column1, t.Column2,
    SUBSTRING(t.TRACKING_NUMBER, n.Number, 18) AS TRACKING_NUMBER
FROM YourTableName t
JOIN Numbers n ON n.Number <= LEN(t.TRACKING_NUMBER) 
    AND (n.Number - 1) % 18 = 0
WHERE t.TRACKING_NUMBER IS NOT NULL;

2. MySQL #

递归CTE方式（MySQL 8.0+支持） #

WITH RECURSIVE SplitCTE AS (
    SELECT 
        Column1, Column2,
        TRACKING_NUMBER,
        1 AS StartPos
    FROM YourTableName
    WHERE TRACKING_NUMBER IS NOT NULL AND LENGTH(TRACKING_NUMBER) >= 18
    UNION ALL
    SELECT 
        Column1, Column2,
        TRACKING_NUMBER,
        StartPos + 18
    FROM SplitCTE
    WHERE StartPos + 18 <= LENGTH(TRACKING_NUMBER)
)
SELECT 
    Column1, Column2,
    SUBSTRING(TRACKING_NUMBER, StartPos, 18) AS TRACKING_NUMBER
FROM SplitCTE
ORDER BY Column1;

低版本MySQL（无CTE） #

SELECT 
    t.Column1, t.Column2,
    SUBSTRING(t.TRACKING_NUMBER, (n.i - 1)*18 + 1, 18) AS TRACKING_NUMBER
FROM YourTableName t
CROSS JOIN (
    SELECT 1 i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 -- 按需扩展运单数量上限
) n
WHERE (n.i - 1)*18 + 1 <= LENGTH(t.TRACKING_NUMBER);

3. Oracle #

CONNECT BY方式 #

SELECT 
    Column1, Column2,
    SUBSTR(TRACKING_NUMBER, (LEVEL - 1)*18 + 1, 18) AS TRACKING_NUMBER
FROM YourTableName
WHERE TRACKING_NUMBER IS NOT NULL
CONNECT BY LEVEL <= CEIL(LENGTH(TRACKING_NUMBER)/18)
    AND PRIOR Column1 = Column1 -- 用主键关联避免笛卡尔积
    AND PRIOR SYS_GUID() IS NOT NULL; -- 防止循环

递归CTE方式 #

WITH SplitCTE AS (
    SELECT 
        Column1, Column2,
        TRACKING_NUMBER,
        1 AS StartPos
    FROM YourTableName
    WHERE TRACKING_NUMBER IS NOT NULL AND LENGTH(TRACKING_NUMBER) >= 18
    UNION ALL
    SELECT 
        Column1, Column2,
        TRACKING_NUMBER,
        StartPos + 18
    FROM SplitCTE
    WHERE StartPos + 18 <= LENGTH(TRACKING_NUMBER)
)
SELECT 
    Column1, Column2,
    SUBSTR(TRACKING_NUMBER, StartPos, 18) AS TRACKING_NUMBER
FROM SplitCTE
ORDER BY Column1;

关键注意事项 #

• 替换YourTableName为实际表名，Column1, Column2为需要保留的其他列。
• 若运单编号长度不是18位，将所有18替换为实际固定长度即可。
• 若存在运单总长度不是固定长度整数倍的情况，建议提前过滤或处理（比如只提取完整编号）。

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