mysql - mysql计数联接

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

因此，基本上我需要一个查询，该查询将获取所有活动，但同时不获取该活动的反应，而是获取COUNT个反应，这在另一个名为activityReactions的表中找到，我该如何做（请参见上面的查询）我想到的）。

因此查询应返回：

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时，它现在应该在responseCount上返回“ 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个反应，则reactionCount = 6，有2个反应，reactionCount = 4等。

最佳答案

您的查询只需要一个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;

关于mysql - mysql计数联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8296198/