如何在MySQL 8中使用递归CTE获取带日期的多表层级信息？

云原生阿岚

2026-7-12

基于特定日期递归获取员工及其下属数据问题

通常获取员工上下级关系数据用普通连接或子查询即可，但难点在于基于指定日期获取对应职级和上级关系——我之前常用ORDER BY DESC加LIMIT 1来取最新生效记录，但这在MySQL 8的递归CTE中无法直接操作。员工的职级（user_level）和上级ID分别存储在两个带start_date的独立表中，需要根据指定日期拿到对应信息。当前编写的递归查询报错，预期结果为：给定用户ID 3，获取该用户及其所有“下属”的数据。

示例数据

CREATE TABLE `users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `firstname` VARCHAR(100),
    `lastname` VARCHAR(100),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO users (firstname, lastname)
VALUES
    ('John', 'Doe'),
    ('Jane', 'Doe'),
    ('Bob', 'Smith'),
    ('Alice', 'Smith'),
    ('Tom', 'Jones'),
    ('Mary', 'Johnson'),
    ('David', 'Brown'),
    ('Karen', 'Davis'),
    ('Richard', 'Miller'),
    ('Susan', 'Wilson'),
    ('Paul', 'Moore'),
    ('Betty', 'Taylor'),
    ('George', 'Anderson'),
    ('Lisa', 'Thomas'),
    ('Kenneth', 'Jackson'),
    ('Angela', 'White'),
    ('Steven', 'Harris'),
    ('Ruth', 'Martin'),
    ('Brian', 'Thompson'),
    ('Dorothy', 'Young');


CREATE TABLE `user_parent` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `parent_id` INT NOT NULL,
    `start_date` DATE NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO user_parent (user_id, parent_id, start_date)
VALUES
    (1, 2, '2020-01-02'),
    (1, 3, '2021-06-22'),
    (3, 4, '2022-05-08'),
    (4, 3, '2020-11-23'),
    (5, 3, '2021-08-19'),
    (6, 3, '2023-03-28'),
    (7, 4, '2022-07-12'),
    (7, 5, '2021-10-31'),
    (9, 10, '2020-09-11'),
    (10, 11, '2022-02-14'),
    (11, 12, '2021-03-17'),
    (12, 13, '2023-06-28'),
    (13, 5, '2020-08-27'),
    (14, 5, '2021-01-30'),
    (15, 5, '2022-04-05'),
    (16, 13,'2023-07-18'),
    (17 ,15,'2020-06-16'),
    (18 ,15,'2022-01-25'),
    (19 ,20,'2021-02-28'),
    (20 ,1,'2023-05-09');
    
 CREATE TABLE `user_levels` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `user_level` INT NOT NULL,
    `start_date` DATE NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO user_levels (user_id, user_level, start_date)
VALUES
    (1, 100, '2020-01-02'),
    (2, 200, '2021-06-22'),
    (3, 400, '2021-01-01'),
    (3, 500, '2022-05-08'),
    (4, 400, '2020-11-23'),
    (5, 500, '2021-08-19'),
    (6, 100, '2023-03-28'),
    (7, 200, '2022-07-12'),
    (8, 300, '2021-10-31'),
    (9, 400, '2020-09-11'),
    (10, 500, '2022-02-14'),
    (11, 100, '2021-03-17'),
    (12, 200, '2023-06-28'),
    (13, 300, '2020-08-27'),
    (14, 400, '2021-01-30'),
    (15, 300,'2021-02-05'),
    (15, 500,'2022-04-05'),
    (16 ,100,'2023-07-18'),
    (17 ,200,'2020-06-16'),
    (18 ,300,'2022-01-25'),
    (19 ,400,'2021-02-28'),
    (20 ,500,'2023-05-09');

原错误查询

WITH RECURSIVE cte (id,firstname,lastname,user_level,parent_id,start_date,depth) AS (
                SELECT u.id,u.firstname,u.lastname,(SELECT user_level FROM user_levels WHERE user_id = 3 AND start_date <= '2023-08-25' ORDER BY start_date DESC LIMIT 1),0 as parent_id,'2023-08-22' as start_date,0 as depth
                    FROM users u
                    WHERE u.id=3
                    
                UNION ALL
                
                SELECT u.id,u.firstname,u.lastname,(SELECT user_level FROM user_levels WHERE user_id = u.id AND start_date <= '2023-08-25' ORDER BY start_date DESC LIMIT 1) as level_code,up.parent_id,up.start_date,cte.depth+1
                FROM users u,cte
                INNER JOIN LATERAL
                (SELECT user_id, parent_id, MAX(start_date) as start_date
                    FROM user_parent p
                    WHERE p.parent_id = cte.id AND p.start_date <= '2023-08-25' GROUP BY p.user_id)
                AS up
                )
SELECT * FROM cte;

修正后的解决方案

思路说明

先预处理每个用户在指定日期（2023-08-25）的有效职级和上级关系，用窗口函数ROW_NUMBER()筛选出每个用户最新生效的记录，避免递归中反复执行子查询
修正递归CTE的关联逻辑，确保递归时正确关联上下级关系
锚点为目标用户ID=3，递归遍历所有下属

完整查询

SET @target_date = '2023-08-25';
SET @target_user = 3;

WITH 
-- 预处理每个用户在指定日期的最新职级
user_current_level AS (
    SELECT 
        user_id, 
        user_level,
        start_date
    FROM (
        SELECT 
            user_id, 
            user_level,
            start_date,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY start_date DESC) AS rn
        FROM user_levels
        WHERE start_date <= @target_date
    ) t
    WHERE rn = 1
),
-- 预处理每个用户在指定日期的直接上级
user_current_parent AS (
    SELECT 
        user_id, 
        parent_id,
        start_date
    FROM (
        SELECT 
            user_id, 
            parent_id,
            start_date,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY start_date DESC) AS rn
        FROM user_parent
        WHERE start_date <= @target_date
    ) t
    WHERE rn = 1
),
-- 递归CTE获取所有下属
recursive_hierarchy AS (
    -- 锚点：目标用户
    SELECT 
        u.id,
        u.firstname,
        u.lastname,
        ul.user_level,
        0 AS parent_id,  -- 目标用户无上级，设为0
        ul.start_date AS level_start_date,
        0 AS depth
    FROM users u
    JOIN user_current_level ul ON u.id = ul.user_id
    WHERE u.id = @target_user
    
    UNION ALL
    
    -- 递归：获取当前节点的下属
    SELECT 
        u.id,
        u.firstname,
        u.lastname,
        ul.user_level,
        up.parent_id,
        ul.start_date AS level_start_date,
        rh.depth + 1 AS depth
    FROM recursive_hierarchy rh
    JOIN user_current_parent up ON rh.id = up.parent_id
    JOIN users u ON up.user_id = u.id
    JOIN user_current_level ul ON u.id = ul.user_id
)
SELECT * FROM recursive_hierarchy;

修正点说明

预处理数据：用窗口函数提前筛选出每个用户在指定日期的最新职级和上级，比递归中嵌套子查询更高效，也避免了递归CTE中不支持LIMIT的问题
递归关联逻辑：原查询的表连接语法混乱（同时用逗号和INNER JOIN），修正后通过user_current_parent关联递归节点和下属，确保逻辑正确
字段一致性：锚点和递归部分的字段名、类型保持一致，避免递归报错

预期结果

返回用户ID=3及其所有下属（包括ID为1、4、5、6、7、13、14、15、16、17、18、20的用户），每个用户的职级为2023-08-25当天生效的最新值，depth字段表示该用户与目标用户的层级距离（目标用户depth为0，直接下属depth为1，以此类推）。

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

如何在MySQL 8中使用递归CTE获取带日期的多表层级信息？

示例数据 #

原错误查询 #

修正后的解决方案 #

思路说明 #

完整查询 #

修正点说明 #

预期结果 #