mysql查询三重加入＆＃x2B;三重计数

Question

我需要执行查询，但不能使其可行... 任何帮助将不胜感激！
在MySQL Workbench上执行
得到了一个由4个表制成的数据库：

用户	帖子	喜欢	注释
用户	名ID ID	ID	ID
ID	标题	userId（来自用户。ID的外键）	内容
电子邮件email	我	postid（来自posts.id from posts.id）	日期
密码	日期日期		userid（from user键。 ID）
Image	ImageUrl		PostID（来自posts.id的外键）
Bio	UserID（user.id的外键）
创建的

我想要一个查询，可以从用户中获取特定数据，以及特定用户的总数，帖子和评论的总数，如果该用户中不存在LIKE /帖子 /注释，则总数为0。 我基本上需要用户的统计数据。

目前，我的查询为我提供了用户名 /允许 / image / bio和创建< / em>的良好信息，但所有计数< / strong>返回我 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';

感谢您的时间＆lt; 3！

Answer 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';