mysql - 有没有办法改进此 SQL 查询以按特定顺序检索数据？

Question

我有一个包含以下 2 个表的数据库

帖子:

|post_id| text |post_date(time_stamp)|

喜欢:

| like_id | likee (1 or -1) | post_id | like_date (time_stamp) |

我正在尝试恢复过去一小时内所有被点赞的帖子，并根据他们在过去一小时内收到的点赞数对它们进行排序。

我尝试了以下查询，它返回了过去一小时内所有被点赞的帖子，但我一直无法弄清楚如何按每个 post_id 的点赞总数来排序。

SELECT * FROM posts
  INNER JOIN likes ON posts.post_id = likes.post_id
  WHERE TIMESTAMPDIFF(HOUR,like_time,NOW())< 1 AND likee= 1

有什么方法可以通过修改查询来实现吗？

Answer 1

首先计算最后一小时的点赞数(或正面点赞数):

 SELECT post_id, 
        SUM( likee ) as total_likes,
        SUM( CASE WHEN likee = 1 THEN 1 ELSE 0 END ) as total_positive_likes
 FROM likes
 WHERE like_date >= DATE_SUB(NOW(),INTERVAL 1 HOUR)
 GROUP BY post_id; 

请注意，您没有更改字段 like_date，而是从 NOW() 创建常量，这样您就可以使用索引。

现在你加入了你的帖子，总点赞数

SELECT p.*, l.total_likes
FROM Post p 
JOIN ( SELECT post_id, 
              SUM( likee ) as total_likes,
              SUM( CASE WHEN likee = 1 THEN 1 ELSE 0 END ) as total_positive_likes
       FROM likes
       WHERE like_date >= DATE_SUB(NOW(),INTERVAL 1 HOUR)
       GROUP BY post_id
    ) L
  ON p.post_id = l.post_id
ORDER BY l.total_likes DESC