用mysql连接5个表
用mysql连接5个表
提问于 2013-01-11 18:53:16
我正在尝试构建一个查询，为每个帖子检索其各自的图片、评论、点赞和收藏数量。所有这些数据都存储在单独的关系表中，这些表总是存储帖子的id。
但是，当我运行该查询时，3个其他页面提供的数据与第一个页面完全相同。
到目前为止，我已经对此进行了编码
SELECT * FROM
(SELECT pst.* FROM posts as pst
LEFT JOIN postcategories as pstcat
ON pst.id = pstcat.id_post
WHERE pst.date IS NOT NULL AND pst.date >= '2013-01-01 00:00:00' AND pst.date <= '2013-01-11 00:00:00' ) as res1
INNER JOIN
(SELECT pst.*, count(pstcmt.id) as cmtCount
FROM posts as pst
LEFT JOIN userpostcomments as pstcmt
ON pst.id = pstcmt.id_post
GROUP BY pst.id) as res2 ON res1.id = res2.id
INNER JOIN
(SELECT pst.id, count(pstlk.id) as lkCount
FROM posts as pst
LEFT JOIN userpostlikes as pstlk
ON pst.id = pstlk.id_post
GROUP BY pst.id) as res3 ON res1.id = res3.id
INNER JOIN
(SELECT pst.id, count(pstfav.id) as favCount
FROM posts as pst
LEFT JOIN userpostfavorites as pstfav
ON pst.id = pstfav.id_post
GROUP BY pst.id) as res4 ON res1.id = res4.id
INNER JOIN
(SELECT pst.id, count(pstimg.id) as imgCount
FROM posts as pst
LEFT JOIN postimages as pstimg
ON pst.id = pstimg.id_post
GROUP BY pst.id) as res5 ON res1.id = res5.id
WHERE res1.deleted = 0
AND res1.date IS NOT NULL
GROUP BY res1.id
ORDER BY res1.date DESC 谢谢
更新:更新了建议的ORDER BY查询。然而，数据镜像的问题仍然存在
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-01-11 19:03:56
也许，这就是你想要的：
SELECT pst.id,
COALESCE(cmtCount, 0) cmtCount,
COALESCE(lkCount, 0) lkCount,
COALESCE(pstfavCount, 0) pstfavCount,
COALESCE(imgCount , 0) imgCount,
FROM posts as pst
LEFT JOIN
(SELECT id_post, count(id) as cmtCount
FROM userpostcomments GROUP BY id_post) as cmt
ON pst.id = cmt.id_post
LEFT JOIN
(SELECT id_post, count(id) as lkCount
FROM userpostlikes GROUP BY id_post) as lk
ON pst.id = lk.id_post
LEFT JOIN
(SELECT id_post, count(id) as pstfavCount
FROM userpostfavorites GROUP BY id_post) as pstfav
ON pst.id = pstfav.id_post
LEFT JOIN
(SELECT id_post, count(id) as imgCount
FROM postimages GROUP BY id_post) as img
ON pst.id = img.id_post
WHERE pst.deleted = 0
AND pst.date >= '2013-01-01 00:00:00'
AND pst.date <= '2013-01-11 00:00:00'
ORDER BY pst.date DESC 我认为，这个查询速度更快。
SELECT pst.id,
COUNT(DISTINCT cmt.id) cmtCount,
COUNT(DISTINCT lk.id) lkCount,
COUNT(DISTINCT pstfav.id) pstfavCount,
COUNT(DISTINCT img.id) imgCount,
FROM posts as pst
LEFT JOIN userpostcomments cmt
ON pst.id = cmt.id_post
LEFT JOIN userpostlikes lk
ON pst.id = lk.id_post
LEFT JOIN userpostfavorites pstfav
ON pst.id = pstfav.id_post
LEFT JOIN postimages img
ON pst.id = img.id_post
WHERE pst.deleted = 0
AND pst.date >= '2013-01-01 00:00:00'
AND pst.date <= '2013-01-11 00:00:00'
GROUP BY pst.id
ORDER BY pst.date DESC 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14276597
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号