如何在sqlite中对层次数据逐级生成序列号？

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    parent_department_id INTEGER,
    department_name TEXT
);

WITH RECURSIVE cte (department_id, parent_department_id, department_name, level, sequence) AS (
    SELECT department_id, parent_department_id, department_name, 1, CAST(department_id AS TEXT)
    FROM departments
    WHERE parent_department_id IS NULL
    UNION ALL
    SELECT d.department_id, d.parent_department_id, d.department_name, c.level + 1, c.sequence || '.' || CAST(d.department_id AS TEXT)
    FROM departments d
    INNER JOIN cte c ON d.parent_department_id = c.department_id
)
SELECT * FROM cte;

UPDATE departments
SET sequence = (
    SELECT sequence
    FROM (
        WITH RECURSIVE cte (department_id, parent_department_id, department_name, level, sequence) AS (
            SELECT department_id, parent_department_id, department_name, 1, CAST(department_id AS TEXT)
            FROM departments
            WHERE parent_department_id IS NULL
            UNION ALL
            SELECT d.department_id, d.parent_department_id, d.department_name, c.level + 1, c.sequence || '.' || CAST(d.department_id AS TEXT)
            FROM departments d
            INNER JOIN cte c ON d.parent_department_id = c.department_id
        )
        SELECT sequence
        FROM cte
        WHERE cte.department_id = departments.department_id
        ORDER BY level DESC
        LIMIT 1
    )
)