MySQL 返回每个父项及其子项的列表

Question

我有如下表格:

Manager 是父级，在他下面有子资源，这些子资源连接到 manager 的父级 ID。

id    | parent_id   |   Role       | Number of roles

123   | 123         | Manager      |    2

124   | 123         | Resource     |    1

125   | 123         | Resource     |    2

128   | 128         | Manager      |    1

126   | 128         | Resource     |    4

127   | 128         | Resource     |    3

我的 sql 查询应该为每个父项返回以下结果:

id    | parent_id   |   Role       | Sum of manager only | Total resources

123   | 123         | Manager      |    2                |    2+1+2 = 5 (Manager + the total number of resources for parent id 123)   

128   | 128         | Manager      |    1                |    1+4+3 = 8 (Manager + the total number of resources for parent id 128)   

如有任何帮助，我们将不胜感激。谢谢。

Answer 1

我希望这样做:

SELECT 
    m1.*, m2.sum_roles
FROM
    managers_workers m1
        LEFT JOIN
    (SELECT 
        parent_id, SUM(num_of_roles) AS sum_roles
    FROM
        managers_workers
    GROUP BY parent_id) AS m2 ON m1.id = m2.parent_id
WHERE
    m2.parent_id IS NOT NULL
;