根据我的理解，这是我提出的查询：
SELECT name, membersCount, IFNULL(totalCount, 0) goalsCount FROM
(
SELECT m.team_id, SUM(innerQuery.goalsCount) totalCount
FROM (
SELECT m.id memberId, COUNT(*) goalsCount
FROM Members m
JOIN Goals g
ON m.id = g.member_id
GROUP BY member_id
) innerQuery
JOIN Members m
ON innerQuery.memberId = m.id
GROUP BY m.team_id
) inner_1
RIGHT JOIN
(
SELECT t.id, t.name, COUNT(*) membersCount
FROM Teams t
JOIN Members m
ON t.id = m.team_id
GROUP BY team_id
) inner_2
ON inner_1.team_id = inner_2.id查询细分：
#1。获取具有关联目标的成员ID(innerQuery)
SELECT m.id memberId, COUNT(*) goalsCount
FROM Members m
JOIN Goals g
ON m.id = g.member_id
GROUP BY member_id#2。获取团队ID以及目标的总和(inner_1)
SELECT m.team_id, SUM(innerQuery.goalsCount) totalCount
FROM (
.... Sub-query in step 1
) innerQuery
JOIN Members m
ON innerQuery.memberId = m.id
GROUP BY m.team_id#3。获得每个团队的成员总数(inner_2)
SELECT t.id, t.name, COUNT(*) membersCount
FROM Teams t
JOIN Members m
ON t.id = m.team_id
GROUP BY team_id#4。 RIGHT JOIN inner_1和inner_2(因为会有NULL)并使用IFNULL检查并替换0
SELECT name, membersCount, IFNULL(totalCount, 0) goalsCount FROM
(
.... Sub-query in step 2
) inner_1
RIGHT JOIN
(
.... Sub-query in step 3
) inner_2
ON inner_1.team_id = inner_2.id
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
