mysql - 按照另一个表中另外两列的计数总和的顺序获取表行

Question

我有 3 个 MYSQL 表，它们是:

发布

mysql> DESCRIBE `posts`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| post_id          | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| post_details     | varchar(11)      | NO   |     | NULL    |                |                |
+------------------+------------------+------+-----+---------+----------------+

喜欢

mysql> DESCRIBE `likes`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id          | int(11)          | NO   |     | NULL    |                |
| user_id          | int(11)          | NO   |     | NULL    |                |                |
+------------------+------------------+------+-----+---------+----------------+

评论

mysql> DESCRIBE `comments`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id          | int(11)          | NO   |     | NULL    |                |
| user_id          | int(11)          | NO   |     | NULL    |                |                |
+------------------+------------------+------+-----+---------+----------------+

在第二个和第三个表中，我存储了帖子的点赞和评论(第一个表)。现在，我想按喜欢和评论的总和降序排列帖子。即帖子将按最大点赞数 + 评论数到最小数量的顺序排列。请帮助我获得正确的结果并提前致谢。

Answer 1

SELECT posts.post_id, post_details,
    ((SELECT(COUNT(id) FROM comments WHERE comments.post_id = posts.post_id)) + (SELECT(COUNT(id) FROM likes WHERE likes.post_id = posts.post_id))) AS weight
FROM posts
ORDER BY weight DESC

在不使用变量的情况下，您可以使用嵌套选择