我有两个表Team和Tasks。任务被分配给每个团队,我想要一个SQL(MySQL(查询来根据Total和Completed Task计数按团队分组任务,如下所示。如果计数在任何情况下为零(总计数和已完成计数(,我也希望有零值
团队表结构
Team_ID int,
Team_Name varchar(50)
任务表
Task_ID int,
Task_Name varchar(100),
Team_ID int (fk)
Completed bit (boolean Yes/No)
预期结果表
团队名称 | 总任务数 | 已完成任务数|
---|---|---|
惊奇 | 6 | 4|
董事 | 4 | 2 |
指挥官 | 2 | 0 |
退伍军人 | 0 | 0 |
新手 | 3 | 1 |
SELECT
tp.teamName 'Team Name',
IFNULL(final.cnt, 0) 'Total Tasks',
IFNULL(comp, 0) 'Completed Tasks Count'
FROM
teams tp
LEFT OUTER JOIN
(SELECT
t.teamId,
t.teamName,
COUNT(tk.task_id) cnt,
IFNULL(b.Comp, 0) comp
FROM
teams t
JOIN tasks tk ON t.teamId = tk.team_id
LEFT OUTER JOIN (SELECT
tkp.team_id, COUNT(tkp.task_id) Comp
FROM
tasks tkp
WHERE
tkp.Completed = '1'
GROUP BY tkp.team_id) b ON b.team_id = t.teamId
GROUP BY t.teamId) final ON tp.teamId = final.teamid;