我有两个表“ 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