mysql 查询下级数量

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
);

WITH RECURSIVE department_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM departments d
    JOIN department_tree dt ON d.parent_id = dt.id
)
SELECT parent_id, COUNT(*) AS child_count
FROM department_tree
GROUP BY parent_id;

SELECT d1.parent_id, COUNT(d2.id) AS child_count
FROM departments d1
LEFT JOIN departments d2 ON d2.parent_id = d1.id
GROUP BY d1.parent_id;

SELECT parent_id, (
    SELECT COUNT(*)
    FROM departments d2
    WHERE d2.parent_id = d1.id
) AS child_count
FROM departments d1
GROUP BY parent_id;