sql - MYSQL 查询三重 join + 三重 count

我需要执行查询,但无法使其工作...任何帮助将不胜感激!

在 MYSQL WORKBENCH

上执行我得到了一个由 4 个组成的数据库 tables:

user Posts likes comments
username id id id
id title userId (foreign key from user.id) content
email content postId (foreign key from posts.id) date
password date userId (foreign key from user.id)
image imageUrl postId (foreign key from posts.id)
bio userId (foreign key from user.id)
createDate

我想要一个查询,该查询将从 USER 获取具体数据加上特定用户的总 count 喜欢、帖子和评论,如果此用户中不存在喜欢/帖子/评论,则总结果为 0。我基本上需要我的用户的统计数据。

我目前的查询为我提供了有关用户名/权限/图像/生物和 createDate 的良好信息,但对所有 counts 都返回 0。

SELECT u.username, u.permission, u.image, u.bio, u.createDate,
COALESCE(P.totalPosts, 0) AS totalPosts,
COALESCE(C.totalComms, 0) AS totalComms,
COALESCE(L.totalLikes, 0) AS totalLikes
FROM user AS u 
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalPosts
    FROM posts
) AS P ON P.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalComms
    FROM comments
) AS C ON C.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalLikes
    FROM likes
) AS L ON L.userId = u.id
WHERE u.username = 'Alfred';

感谢您抽出时间<3!

回答1

你缺少的是:

GROUP BY userId

在您的所有子查询中:

SELECT u.username, u.permission, u.image, u.bio, u.createDate,
       COALESCE(P.totalPosts, 0) AS totalPosts,
       COALESCE(C.totalComms, 0) AS totalComms,
       COALESCE(L.totalLikes, 0) AS totalLikes
FROM user AS u 
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalPosts
    FROM posts
    GROUP BY userId
) AS P ON P.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalComms
    FROM comments
    GROUP BY userId
) AS C ON C.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalLikes
    FROM likes
    GROUP BY userId
) AS L ON L.userId = u.id
WHERE u.username = 'Alfred';

但是,所有这些子查询都会为所有用户进行聚合,而不仅仅是您想要结果的特定用户,这对性能不利。

如果您使用相关子查询会更好:

SELECT u.username, u.permission, u.image, u.bio, u.createDate,
       (SELECT COUNT(*) FROM posts p WHERE p.userId = u.id) AS totalPosts,
       (SELECT COUNT(*) FROM comments c WHERE c.userId = u.id) AS totalComms,
       (SELECT COUNT(*) FROM likes l WHERE l.userId = u.id) AS totalLikes
FROM user AS u
WHERE u.username = 'Alfred';

相似文章