mysql计数加入

Question

SELECT A.id as ActivityId, A.description, T.id, T.title, COUNT(R.*) as reactionCount
FROM activities 
LEFT JOIN activitiesReactions as R ON R.activityId = A.id
LEFT JOIN activitiesTags as T ON A.tagId = T.id

所以基本上我需要一个查询来获取所有事件，同时不获取该事件的 react ，而是获取 react 的 COUNT，这些 react 位于另一个名为 activitiesReactions 的表中，我该怎么做(请参阅上面我想到的查询)。

所以查询应该返回:

array('activityId' => 3, 'description' => 'doing work', 'reactionCount' => 2)

示例行:

Activities table:
id | description
 3   doing work
 4   checking mail

ActivitiesReactions table:
id | activityId | message
 1            3   you never do anywork, so that must be bullshit.
 2            3   yes I do alot of work!

所以现在当我执行查询并执行 WHERE A.id = 3 时，它应该在 reactionCount 上返回“2”

---

SELECT A.id as ActivityId, A.description, COUNT(R.activityId) AS reactionCount
FROM activities 
LEFT JOIN activitiesReactions as R
ON R.activityId = A.id
GROUP BY A.id

这确实有效，但 reactionCount 返回 * 2，因此例如，如果有 3 个 react ，则 reactionCount = 6，如果有 2 个 react ，则 reactionCount = 4 等。

Answer 1

您的查询只需要一个 group by 子句即可使其工作，例如。

SELECT A.id as ActivityId, A.description, COUNT(R.*) 
FROM activities 
    LEFT JOIN activitiesReactions as R ON R.activityId = A.id
GROUP BY A.id, A.description;