如何在SQL Server 2016中生成层级子路径的5列展示

针对SQL Server 2016的环境，你可以通过直接计算分隔符>的位置来逐列截取路径内容，不需要复杂的拆分拼接逻辑，具体SQL代码如下：

SELECT
    DomainPath,
    -- 第1列：根节点（第一个子域名）
    CASE 
        WHEN DomainPath IS NULL THEN NULL
        ELSE LEFT(DomainPath, CHARINDEX('>', DomainPath + '>') - 1) 
    END AS Col1,
    -- 第2列：根节点+第二个子域名
    CASE 
        WHEN DomainPath IS NULL THEN NULL
        WHEN CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath) + 1) = 0 THEN NULL
        ELSE LEFT(DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath) + 1) - 1) 
    END AS Col2,
    -- 第3列：前三个子域名的组合
    CASE 
        WHEN DomainPath IS NULL THEN NULL
        WHEN CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath) + 1) + 1) = 0 THEN NULL
        ELSE LEFT(DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath) + 1) + 1) - 1) 
    END AS Col3,
    -- 第4列：前四个子域名的组合
    CASE 
        WHEN DomainPath IS NULL THEN NULL
        WHEN CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath) + 1) + 1) + 1) = 0 THEN NULL
        ELSE LEFT(DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath) + 1) + 1) + 1) - 1) 
    END AS Col4,
    -- 第5列：前五个子域名的组合（路径不足5个节点则为NULL）
    CASE 
        WHEN DomainPath IS NULL THEN NULL
        -- 节点数=分隔符数量+1，所以分隔符≥4时才有至少5个节点
        WHEN (LEN(DomainPath) - LEN(REPLACE(DomainPath, '>', ''))) < 4 THEN NULL
        ELSE LEFT(DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath, CHARINDEX('>', DomainPath) + 1) + 1) + 1) + 1) - 1) 
    END AS Col5
FROM YourTable;

逻辑说明 #

1. Col1：通过CHARINDEX找到第一个>的位置，截取该位置之前的内容；若路径为NULL或空串，返回NULL。
2. Col2-Col4：依次嵌套调用CHARINDEX，找到第2、3、4个>的位置，截取对应前缀；如果找不到对应数量的分隔符，返回NULL。
3. Col5：先判断路径中>的数量是否≥4（即节点数≥5），如果是，找到第5个>的位置并截取前缀；否则返回NULL。

测试场景验证 #

• 当路径为Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5时，Col3会返回Subdomain1>Subdomain2>Subdomain3，符合要求。
• 当路径为Subdomain1>Subdomain2时，Col3、Col4、Col5均为NULL。
• 当路径为Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5>Subdomain6时，Col5返回前5个节点的组合Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5。
• 当路径为NULL时，所有列均为NULL。

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