SQL Server多主键表递归SELECT去重优化方案咨询

问题原因 #

你的表主键包含NR_VALUE，导致同一个**父ID（ID_ORIGIN）-子ID（ID_NEW）**组合对应多条记录。递归查询时，每一条记录都会触发一次关联，最终生成大量重复的层级关系行。

解决方案：从根源避免重复行生成 #

不需要在最终结果用GROUP BY，而是直接在递归流程中只保留唯一的父-子组合，从源头杜绝重复行产生。

方案1：在递归CTE中直接过滤重复对 #

修改原递归查询，在锚点和递归步骤中通过DISTINCT保留唯一的父-子对：

DROP TABLE IF EXISTS #TABLE

CREATE TABLE #TABLE
(
    ID_ORIGIN VARCHAR(15), --pk
    ID_NEW VARCHAR(15), --pk
    NR_VALUE VARCHAR(3), --pk
    VLR_PRICE FLOAT
)

INSERT INTO #TABLE
VALUES
('1111111111', '2222222222', '001', 100),
('1111111111', '2222222222', '002', 120),
('1111111111', '2222222222', '003', 130),
('1111111111', '2222222222', '004', 140),

('2222222222', '3333333333', '001', 150),
('2222222222', '3333333333', '002', 160),
('2222222222', '3333333333', '003', 170),
('2222222222', '3333333333', '004', 180),

('3333333333', '4444444444', '001', 190),
('3333333333', '4444444444', '002', 200),
('3333333333', '4444444444', '003', 220),
('3333333333', '4444444444', '004', 230),

('4444444444', '5555555555', '001', 240),
('4444444444', '5555555555', '002', 250),
('4444444444', '5555555555', '003', 270),
('4444444444', '5555555555', '004', 280)

;WITH Niveis AS 
(
    -- 锚点：只取唯一的父-子对
    SELECT DISTINCT 
        ID_NEW, 
        ID_ORIGIN, 
        0 AS Level
    FROM #TABLE
    WHERE ID_ORIGIN = '1111111111'

    UNION ALL

    -- 递归：关联唯一的父-子对，避免重复递归
    SELECT DISTINCT
        T1.ID_NEW, 
        T1.ID_ORIGIN, 
        Level + 1 AS Level
    FROM #TABLE T1
    INNER JOIN Niveis ON T1.ID_ORIGIN = Niveis.ID_NEW
)
SELECT ID_ORIGIN, ID_NEW, Level
FROM Niveis
ORDER BY ID_ORIGIN, ID_NEW

方案2：提前生成去重的父-子关系表（大表场景更优） #

如果数据量较大，先提前生成仅包含唯一父-子对的临时表，再基于该表做递归，能减少递归过程中的关联次数，进一步提升性能：

DROP TABLE IF EXISTS #TABLE
DROP TABLE IF EXISTS #ParentChild

CREATE TABLE #TABLE
(
    ID_ORIGIN VARCHAR(15), --pk
    ID_NEW VARCHAR(15), --pk
    NR_VALUE VARCHAR(3), --pk
    VLR_PRICE FLOAT
)

INSERT INTO #TABLE
VALUES
('1111111111', '2222222222', '001', 100),
('1111111111', '2222222222', '002', 120),
('1111111111', '2222222222', '003', 130),
('1111111111', '2222222222', '004', 140),

('2222222222', '3333333333', '001', 150),
('2222222222', '3333333333', '002', 160),
('2222222222', '3333333333', '003', 170),
('2222222222', '3333333333', '004', 180),

('3333333333', '4444444444', '001', 190),
('3333333333', '4444444444', '002', 200),
('3333333333', '4444444444', '003', 220),
('3333333333', '4444444444', '004', 230),

('4444444444', '5555555555', '001', 240),
('4444444444', '5555555555', '002', 250),
('4444444444', '5555555555', '003', 270),
('4444444444', '5555555555', '004', 280)

-- 生成去重的父-子关系表
SELECT DISTINCT ID_ORIGIN, ID_NEW
INTO #ParentChild
FROM #TABLE

;WITH Niveis AS 
(
    -- 锚点：基于去重后的表查询
    SELECT 
        ID_NEW, 
        ID_ORIGIN, 
        0 AS Level
    FROM #ParentChild
    WHERE ID_ORIGIN = '1111111111'

    UNION ALL

    -- 递归：关联去重后的表，无需再DISTINCT
    SELECT
        T1.ID_NEW, 
        T1.ID_ORIGIN, 
        Level + 1 AS Level
    FROM #ParentChild T1
    INNER JOIN Niveis ON T1.ID_ORIGIN = Niveis.ID_NEW
)
SELECT ID_ORIGIN, ID_NEW, Level
FROM Niveis
ORDER BY ID_ORIGIN, ID_NEW

效果说明 #

两种方案都会直接生成无重复的层级关系数据，避免了最终结果集的GROUP BY操作，性能远优于先生成大量重复行再去重的方式。其中方案2对于大表场景性能提升更明显，因为递归的基础数据量大幅减少。

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