按不同表的行排序 - Order by rows of different table

Order by rows of different table

本文关键字：排序 | 更新日期: 2024-03-24

我有带id(int(10) key), user_id(varchar(15)), post_id(varchar(15))的"user_likes"表和带post_id(int(15) key), user_id(varchar(15)), post_txt(text) 的"user_post"表

我现在通过id:订购帖子

$que_posts = mysql_query("select * from user_post order by user_id DESC");

我想按每个帖子的点赞量排序（即点赞数越多的帖子将排在第一位）。

问题是LIKES在不同的表中。我该怎么做？

编辑-我使用sef4eg的答案（有更改）我可以修复它：

"SELECT user_post.*, COUNT(user_likes.post_id) AS like_count
                            FROM user_post LEFT JOIN user_likes 
                            ON user_post.post_id = user_likes.post_id
                            GROUP BY user_post.post_id
                            ORDER BY like_count desc;";

现在您的选择中有like_count，因此如果需要，可以显示它

SELECT user_post.*, COUNT(user_likes.id) as like_count
FROM user_post 
LEFT JOIN user_likes
ON user_likes.user_id = user_post.user_id AND user_likes.post_id = user_post.post_id
GROUP BY user_post.post_id
ORDER BY like_count DESC