mysql - 复杂(?) mysql相关计数查询。请帮忙？

Question

假设以下情况。分配给任务的人员，我想从下表返回人员 ID、人员名称、每个人员完成的任务数。

表名称 - 字段名称

人员 - ID、姓名

Task_Person_Combi - Task_id、Person_id

Task* - 返回任务的 id(实际上这是返回人员 id 的 LEFT 连接表)(任务超过100,000行，查询速度必须快于1秒)

阅读后MySQL statement combining a join and a count? ，我正在尝试以下操作。 (但这似乎不起作用，我有点迷失了)

SELECT id, Name,
(
    SELECT COUNT(*)
    FROM Task_Person_Combi C
    WHERE P.id=C.Person_id AND C.Task IN (SELECT id FROM Task* - this is Joined table)
) AS Count
FROM Person P
WHERE id>0
HAVING Count>0
ORDER BY Name

请帮忙。

Answer 1

试试这个？

SELECT id, Name,
      COUNT(T.ID)  AS TaskCount

FROM Person AS P
INNER JOIN Task_Person_Combi AS C ON  P.id=C.Person_id 
LEFT JOIN TASK AS T ON C.Task = T.id
WHERE id>0
AND T.id IS NOT NULL
GROUP BY id,Name
HAVING COUNT(T.ID)>0
ORDER BY Name