分组字符串片段时规避子查询：高效计算层级产品总价方案

为什么OVER(PARTITION BY)不可行 #

OVER(PARTITION BY)的核心是将数据集划分为互不重叠的独立分组，每组内进行聚合计算。但你的需求是每个节点需要包含自身及所有子节点的价格总和，分组之间是包含关系（父节点的分组要包含子节点的分组），这和PARTITION BY的分组逻辑完全不匹配，因此无法直接用它实现目标。

高效替代方案：递归CTE #

处理层级结构数据的最优方法是使用递归CTE，它能高效遍历层级关系，避免子查询中LIKE前缀匹配带来的多次全表扫描，尤其适合大数据量场景。

实现代码 #

-- 创建测试表（原始测试数据）
drop table if exists #group_test
Create Table #group_test
    (
    ID Integer
    , Hierarchy Nvarchar(200)
    , Price Integer
    )

Insert Into #group_test
values 
    (1,'001',10)
    , (2,'001.001',20)
    , (3,'001.002',5)
    , (4,'001.002.001',3)
    , (5,'001.002.002',2)
    , (6,'001.002.003',4)
    , (7,'001.003',6)

-- 递归CTE计算每个节点的自身+子节点总价
WITH HierarchyCTE AS (
    -- 锚点：每个节点自身作为根节点的初始记录
    SELECT 
        ID,
        Hierarchy,
        Price,
        Hierarchy AS RootHierarchy
    FROM #group_test
    UNION ALL
    -- 递归：遍历当前根节点的所有子节点
    SELECT 
        child.ID,
        child.Hierarchy,
        child.Price,
        parent.RootHierarchy
    FROM HierarchyCTE parent
    JOIN #group_test child
        ON child.Hierarchy LIKE parent.Hierarchy + '.%'
)
-- 按根节点分组求和，关联原表输出完整信息
SELECT 
    t.ID,
    t.Hierarchy,
    t.Price,
    SUM(c.Price) AS Total_Price
FROM #group_test t
JOIN HierarchyCTE c
    ON t.Hierarchy = c.RootHierarchy
GROUP BY t.ID, t.Hierarchy, t.Price
ORDER BY t.Hierarchy;

代码说明 #

1. 锚点成员：将每个节点作为独立的根节点，初始化递归的起始点。
2. 递归成员：通过JOIN找到所有以当前根节点层级为前缀的子节点，将子节点归入对应根节点的分组中。
3. 最终聚合：按根节点的层级分组，求和得到每个节点自身及所有子节点的总价。

性能优化建议 #

给Hierarchy字段创建非聚集索引，可以大幅提升递归过程中LIKE前缀匹配的连接效率：

CREATE NONCLUSTERED INDEX IX_GroupTest_Hierarchy ON #group_test(Hierarchy);

结果验证 #

执行上述代码后，会得到预期结果：

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