我需要執行查詢，但無法使其正常作業...任何幫助將不勝感激！
在 MYSQL WORKBENCH 上執行
我得到了一個由 4 個表組成的資料庫：
| 用戶 | 帖子 | 喜歡 | 評論 |
|---|---|---|---|
| 用戶名 | ID | ID | ID |
| ID | 標題 | userId（來自 user.id 的外鍵） | 內容 |
| 電子郵件 | 內容 | postId（來自posts.id的外鍵） | 日期 |
| 密碼 | 日期 | userId（來自 user.id 的外鍵） | |
| 圖片 | 圖片網址 | postId（來自posts.id的外鍵） | |
| 生物 | userId（來自 user.id 的外鍵） | ||
| 創建日期 |
我想要一個查詢，該查詢將從 USER 獲取具體資料加上特定用戶的喜歡、帖子和評論的總數，如果該用戶中不存在喜歡/帖子/評論，則總結果為 0。我基本上需要我的用戶的統計資料。
我目前的查詢為我提供了有關用戶名/權限/影像/生物和 createDate的良好資訊，但所有計數均回傳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！
uj5u.com熱心網友回復：
你缺少的是：
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';
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/480138.html
上一篇：完全連接兩個資料集，但保留我匹配的兩列，并在不完全匹配時添加新行
下一篇：如何根據十進制列連接兩個表？