不使用String_Split实现SQL字符串拆分、连接与合并的方案咨询

针对你的需求，这里提供几种不依赖STRING_SPLIT函数的SQL实现，适配不同数据库环境：

一、SQL Server 兼容旧版本方案 #

使用递归CTE拆分空格分隔的TranID，再通过FOR XML PATH合并结果：

WITH SplitTranIDs AS (
    SELECT 
        DOID,
        LEFT(TranID, CHARINDEX(' ', TranID + ' ') - 1) AS SingleTranID,
        STUFF(TranID, 1, CHARINDEX(' ', TranID + ' '), '') AS RemainingTranID
    FROM TableDO
    WHERE TranID IS NOT NULL AND TranID != ''
    UNION ALL
    SELECT 
        DOID,
        LEFT(RemainingTranID, CHARINDEX(' ', RemainingTranID + ' ') - 1) AS SingleTranID,
        STUFF(RemainingTranID, 1, CHARINDEX(' ', RemainingTranID + ' '), '') AS RemainingTranID
    FROM SplitTranIDs
    WHERE RemainingTranID IS NOT NULL AND RemainingTranID != ''
)
SELECT 
    s.DOID,
    STUFF((
        SELECT ',' + t.Transporter
        FROM SplitTranIDs s2
        JOIN TblTransporter t ON s2.SingleTranID = t.TranID
        WHERE s2.DOID = s.DOID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Transporters
FROM SplitTranIDs s
GROUP BY s.DOID;

二、MySQL 方案 #

借助数字辅助表拆分字段，用GROUP_CONCAT合并结果：

-- 创建临时数字表（根据实际TranID最大数量调整行数）
CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3),(4),(5);

SELECT 
    td.DOID,
    GROUP_CONCAT(t.Transporter SEPARATOR ',') AS Transporters
FROM TableDO td
JOIN nums n 
    ON n.n <= LENGTH(td.TranID) - LENGTH(REPLACE(td.TranID, ' ', '')) + 1
JOIN TblTransporter t 
    ON t.TranID = SUBSTRING_INDEX(SUBSTRING_INDEX(td.TranID, ' ', n.n), ' ', -1)
GROUP BY td.DOID;

三、Oracle 方案 #

使用CONNECT BY结合正则表达式拆分，通过LISTAGG合并：

SELECT 
    td.DOID,
    LISTAGG(t.Transporter, ',') WITHIN GROUP (ORDER BY t.TranID) AS Transporters
FROM TableDO td
JOIN (
    SELECT 
        DOID,
        REGEXP_SUBSTR(TranID, '[^ ]+', 1, LEVEL) AS SingleTranID
    FROM TableDO
    CONNECT BY LEVEL <= REGEXP_COUNT(TranID, '[^ ]+')
        AND PRIOR DOID = DOID
        AND PRIOR SYS_GUID() IS NOT NULL
) s ON td.DOID = s.DOID
JOIN TblTransporter t ON s.SingleTranID = t.TranID
GROUP BY td.DOID;

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