使用DeepSeek-V3优化SQL递归查询：从性能瓶颈到高效解决方案

WITH RECURSIVE OrgHierarchy AS (
    -- 锚定成员：查找根节点
    SELECT 
        id, 
        name, 
        parent_id,
        CAST(name AS VARCHAR(1000)) AS hierarchy_path
    FROM organization
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归成员：逐层向下查找
    SELECT 
        o.id, 
        o.name, 
        o.parent_id,
        CAST(oh.hierarchy_path || ' > ' || o.name AS VARCHAR(1000))
    FROM organization o
    INNER JOIN OrgHierarchy oh ON o.parent_id = oh.id
)
SELECT * FROM OrgHierarchy
WHERE hierarchy_path LIKE '%技术部%';

-- AI建议的数据库结构调整
ALTER TABLE organization ADD COLUMN path VARCHAR(1000);
ALTER TABLE organization ADD COLUMN level INT;

-- 创建索引以支持快速路径查询
CREATE INDEX idx_organization_path ON organization(path);
CREATE INDEX idx_organization_level ON organization(level);

-- 更新根节点
UPDATE organization 
SET path = CAST(id AS VARCHAR(1000)), 
    level = 1
WHERE parent_id IS NULL;

-- 递归更新子节点（使用WHILE循环替代CTE，避免超时）
CREATE PROCEDURE UpdateOrganizationPaths()
AS
BEGIN
    DECLARE @continue BIT = 1;
    DECLARE @currentLevel INT = 1;
    
    WHILE @continue = 1
    BEGIN
        UPDATE child
        SET 
            child.path = parent.path + '.' + CAST(child.id AS VARCHAR(20)),
            child.level = parent.level + 1
        FROM organization child
        INNER JOIN organization parent ON child.parent_id = parent.id
        WHERE child.level IS NULL AND parent.level = @currentLevel;
        
        IF @@ROWCOUNT = 0
            SET @continue = 0;
        ELSE
            SET @currentLevel = @currentLevel + 1;
    END
END;

-- 查询技术部及其所有子部门
SELECT *
FROM organization
WHERE path LIKE (
    SELECT path + '%' 
    FROM organization 
    WHERE name = '技术部'
)
ORDER BY path;

CREATE TRIGGER TR_Organization_Path
ON organization
AFTER INSERT, UPDATE
AS
BEGIN
    IF UPDATE(parent_id)
    BEGIN
        UPDATE o
        SET 
            o.path = p.path + '.' + CAST(o.id AS VARCHAR(20)),
            o.level = p.level + 1
        FROM organization o
        INNER JOIN inserted i ON o.id = i.id
        INNER JOIN organization p ON o.parent_id = p.id
        WHERE o.id = i.id;
    END
END;