MySQL 计算 INNER 关系中的项目数

Question

我正在尝试从表中获取一组集合，但我不知道如何使用 LEFT JOIN 语法计算相关表“like_collections”中的点赞数。

正如您在查询中看到的，对于每个 collection_id，我需要获取喜欢此类集合的用户数量。

我知道我只能使用 (SELECT COUNT(*) FROM likes_collections WHERE collection_id=cn.id) as n_likes 但想知道是否可以使用以下查询。

SELECT  cn.id,
        cn.name,
        cn.description,
        u.avatar,
        u.username,
        COUNT(lc.id) as n_likes,
    (SELECT COUNT(*) FROM collection_items WHERE collection_id=cn.id) as n_items
FROM collection_names as cn 
INNER JOIN users as u ON u.ID=cn.user_id 
LEFT JOIN likes_collections as lc ON lc.collection_id=cn.id
WHERE cn.public=1
GROUP BY lc.collection_id
ORDER BY cn.published_date DESC
LIMIT 0, 5

Answer 1

当您有LEFT JOIN时，您不应在GROUP BY中使用子表中的列。这是因为当主表中的行没有匹配项时，该表中的列将全部为 NULL，并且它将把所有这些不匹配的行分组在一起。所以你应该GROUP BY cn.id而不是GROUP BY lc.collection_id。

您可以连接一个子查询来代替相关子查询，该子查询计算按集合 ID 分组的项目数。

SELECT  cn.id,
        cn.name,
        cn.description,
        u.avatar,
        u.username,
        COUNT(lc.id) as n_likes,
        n_items
FROM collection_names as cn 
INNER JOIN users as u ON u.ID=cn.user_id 
LEFT JOIN likes_collections as lc ON lc.collection_id=cn.id
LEFT JOIN (SELECT collection_id, COUNT(*) AS n_items
           FROM collection_items
           GROUP BY collection_id) AS ci ON ci.collection_id = cn.id
WHERE cn.public=1
GROUP BY cn.id
ORDER BY cn.published_date DESC
LIMIT 0, 5