Oracle层级查询：关联两表实现任务层级的最低项目级展示

1. 先明确表结构假设（若你的表结构不同，自行调整字段） #

• PROJECTS 表：project_id（主键）、parent_project_id（父项目ID，顶层为NULL）、project_name
• TASKS 表：task_id（主键）、project_id（关联项目ID）、parent_task_id（父任务ID，顶级任务为NULL）、task_name

2. 步骤1：追溯最低级项目的顶层项目 #

用递归CTE生成所有项目的层级关系，同时标记每个项目对应的顶层父项目：

WITH project_hierarchy AS (
    -- 锚点：顶层项目
    SELECT 
        project_id,
        parent_project_id,
        project_name,
        project_id AS top_project_id,
        0 AS depth
    FROM PROJECTS
    WHERE parent_project_id IS NULL

    UNION ALL

    -- 递归：向下遍历子项目，继承顶层项目ID
    SELECT 
        p.project_id,
        p.parent_project_id,
        p.project_name,
        ph.top_project_id,
        ph.depth + 1 AS depth
    FROM PROJECTS p
    JOIN project_hierarchy ph ON p.parent_project_id = ph.project_id
),
project_top_mapping AS (
    SELECT 
        project_id,
        top_project_id,
        depth AS project_depth
    FROM project_hierarchy
)
SELECT * FROM project_top_mapping;

3. 步骤2：确定每个任务所属的最低级项目 #

这里的"最低项目级"指任务关联项目中层级最深的那个（即最底层执行项目）：

WITH project_hierarchy AS (
    -- 生成项目层级与深度
    SELECT 
        project_id,
        parent_project_id,
        project_id AS top_project_id,
        0 AS depth
    FROM PROJECTS
    WHERE parent_project_id IS NULL

    UNION ALL

    SELECT 
        p.project_id,
        p.parent_project_id,
        ph.top_project_id,
        ph.depth + 1 AS depth
    FROM PROJECTS p
    JOIN project_hierarchy ph ON p.parent_project_id = ph.project_id
),
task_lowest_project AS (
    SELECT 
        t.task_id,
        t.parent_task_id,
        t.task_name,
        t.project_id AS lowest_project_id,
        ph.top_project_id,
        ROW_NUMBER() OVER (PARTITION BY t.task_id ORDER BY ph.depth DESC) AS rn
    FROM TASKS t
    JOIN project_hierarchy ph ON t.project_id = ph.project_id
)
-- 只保留每个task_id对应的最低级项目记录
SELECT 
    task_id,
    parent_task_id,
    task_name,
    lowest_project_id,
    top_project_id
FROM task_lowest_project
WHERE rn = 1;

4. 合并需求：基于顶层项目展示无重复的任务层级 #

结合上述两部分逻辑，用递归CTE展示任务的层级结构：

WITH project_hierarchy AS (
    SELECT 
        project_id,
        parent_project_id,
        project_id AS top_project_id,
        0 AS depth
    FROM PROJECTS
    WHERE parent_project_id IS NULL

    UNION ALL

    SELECT 
        p.project_id,
        p.parent_project_id,
        ph.top_project_id,
        ph.depth + 1 AS depth
    FROM PROJECTS p
    JOIN project_hierarchy ph ON p.parent_project_id = ph.project_id
),
task_lowest_project AS (
    SELECT 
        t.task_id,
        t.parent_task_id,
        t.task_name,
        t.project_id AS lowest_project_id,
        ph.top_project_id,
        ROW_NUMBER() OVER (PARTITION BY t.task_id ORDER BY ph.depth DESC) AS rn
    FROM TASKS t
    JOIN project_hierarchy ph ON t.project_id = ph.project_id
)
SELECT 
    tl.task_id,
    tl.parent_task_id,
    tl.task_name,
    tl.lowest_project_id,
    tl.top_project_id
INTO #filtered_tasks
FROM task_lowest_project tl
WHERE rn = 1;

-- 递归生成任务层级
WITH task_hierarchy AS (
    SELECT 
        task_id,
        parent_task_id,
        task_name,
        lowest_project_id,
        top_project_id,
        0 AS task_depth,
        CAST(task_name AS VARCHAR(1000)) AS task_path
    FROM #filtered_tasks
    WHERE parent_task_id IS NULL

    UNION ALL

    SELECT 
        ft.task_id,
        ft.parent_task_id,
        ft.task_name,
        ft.lowest_project_id,
        ft.top_project_id,
        th.task_depth + 1 AS task_depth,
        CONCAT(th.task_path, ' > ', ft.task_name) AS task_path
    FROM #filtered_tasks ft
    JOIN task_hierarchy th ON ft.parent_task_id = th.task_id
)
-- 按顶层项目分组展示
SELECT 
    top_project_id,
    task_id,
    parent_task_id,
    task_name,
    lowest_project_id,
    task_depth,
    task_path
FROM task_hierarchy
ORDER BY top_project_id, task_depth, task_id;

DROP TABLE #filtered_tasks;

关键说明 #

• ROW_NUMBER() OVER (PARTITION BY t.task_id ORDER BY ph.depth DESC) 是核心逻辑：按task_id分组，取项目深度最大的记录，确保每个任务只保留所属最低级项目的条目。
• 递归CTE同时处理了项目层级追溯和任务层级展示，彻底解决重复任务块问题。
• 若"最低项目级"定义不是深度最大，只需调整ORDER BY后的字段即可适配。

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