我有两个表“ Comments”和“ Likes”,可以显示所有喜欢的注释,并在添加注释时对其进行排序。目前,我似乎无法执行的操作是根据喜欢的时间排序评论。

这是我目前所拥有的:

SELECT *
FROM comments AS c
WHERE EXISTS (
    SELECT *
    FROM likes AS l
    WHERE c.commentID=l.commentID)


谁能帮助我用SQL来显示注释,以便按照最喜欢的注释顺序显示注释,依此类推...

只需添加-我只想显示一次评论,并避免显示任何未被喜欢的评论。

最佳答案

如果给定评论有多个喜欢,则需要汇总,例如:

SELECT c.*
FROM comments c join
     (select l.commentId, MIN(likedate) as FirstLikeDate, MAX(likedate) as MaxLikeDate
      from likes l
      group by l.commentId
     ) l
     on c.commentId = l.CommentId
order by MaxLikeDate desc

10-04 15:52