我有一个表格:
经理是父母,在他的下面有子女资源,并加入了经理的父母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)
任何帮助将不胜感激。谢谢。
我希望这会做到:
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
;
您只需要使用父ID进行自加入即可。如果您的表名是" tbl_manager"
SELECT t1.id,
SUM(IF(t1.id = t2.id, 1,0)) AS is_manager,
t1.parent_id,
t1.Role,
t1.Number_of_Role AS Sum_of_manager_only,
IFNULL(SUM(t2.Number_of_Role),0) AS Total_resources
FROM tbl_manager AS t1
LEFT JOIN tbl_manager AS t2
ON t2.parent_id = t1.id
GROUP BY t1.id
HAVING is_manager;
您可以使用子查询
select
(select id from mytbl where id=r.parent_id) id
,r.parent_id
,(select sum(NofRole) from mytbl where parent_id=r.parent_id and Role='Manager') [Sum of manager only]
, sum(r.nofrole) [Total resources]
from mytbl r
group by r.parent_id