选择帖子，最后3条评论和所有评论的计数 - SELECT posts, its last 3 comments and count of all comments

SELECT posts, its last 3 comments and count of all comments

本文关键字：评论选择最后 3条 | 更新日期: 2023-10-06

选择帖子，最后3条评论和所有评论的计数我的代码：

SELECT p.*, u.id, u.username username, u.usersurname usersurname, u.usermainphoto userphoto, GROUP_CONCAT(c.text SEPARATOR 'a!k@h#md%o^v&') commenttext,GROUP_CONCAT(c.likes SEPARATOR '-') commentlikes,GROUP_CONCAT(c.dislikes SEPARATOR '-') commentdislikes, GROUP_CONCAT(c.commentdate) commentdate, GROUP_CONCAT(u2.username) commentauthorname, GROUP_CONCAT(c.anonim) commentanonym, GROUP_CONCAT(c.id) commentid, GROUP_CONCAT(u2.id) commentauthorid, GROUP_CONCAT(u2.usersurname) commentauthorsurname, GROUP_CONCAT(u2.usermainphoto) commentauthorphoto, GROUP_CONCAT(c.commentphotoid) commentphotoid
FROM posts p
LEFT JOIN comments c ON c.post = p.postid AND c.commentdel=0
LEFT JOIN users u ON u.id = p.postauthorid
LEFT JOIN users u2 ON u2.id = c.author
WHERE p.postwallid = :id AND p.postdel=0
GROUP BY postid
ORDER BY postid DESC

它给了我所有的评论，但我只需要3个

您需要一个时间戳或计数器来确定您想要的三个注释。在下面的尖括号之间添加列名。

SELECT p.*, u.id, u.username username, u.usersurname usersurname, u.usermainphoto userphoto, GROUP_CONCAT(c.text SEPARATOR 'a!k@h#md%o^v&') commenttext,GROUP_CONCAT(c.likes SEPARATOR '-') commentlikes,GROUP_CONCAT(c.dislikes SEPARATOR '-') commentdislikes, GROUP_CONCAT(c.commentdate) commentdate, GROUP_CONCAT(u2.username) commentauthorname, GROUP_CONCAT(c.anonim) commentanonym, GROUP_CONCAT(c.id) commentid, GROUP_CONCAT(u2.id) commentauthorid, GROUP_CONCAT(u2.usersurname) commentauthorsurname, GROUP_CONCAT(u2.usermainphoto) commentauthorphoto, GROUP_CONCAT(c.commentphotoid) commentphotoid
FROM posts p
LEFT JOIN comments c ON c.post = p.postid AND c.commentdel = 0
LEFT JOIN users u ON u.id = p.postauthorid
LEFT JOIN users u2 ON u2.id = c.author
WHERE p.postwallid = :id AND p.postdel = 0
    and (
        select count(*) from comments as c2
        where c2.postid = p.postid and c2.commentdel = 0
        and c2.<timestamp> <= c.timestamp
    ) < 3
GROUP BY postid
ORDER BY postid DESC

编辑：我没有添加所有评论的计数。我认为你可以很容易地在选择列表中添加另一个子查询，但我知道MySQL的人不太喜欢子查询。

(
select count(*) from comments as c2
where c2.postid = c.postid and c2.commentdel = 0
) as comment_count

我添加SELECT计数（postid）作为all_comments，并在末尾LIMIT 0，3

SELECT count(postid) as all_comments, p.*, u.id, u.username username, u.usersurname usersurname, u.usermainphoto userphoto, GROUP_CONCAT(c.text SEPARATOR 'a!k@h#md%o^v&') commenttext,GROUP_CONCAT(c.likes SEPARATOR '-') commentlikes,GROUP_CONCAT(c.dislikes SEPARATOR '-') commentdislikes, GROUP_CONCAT(c.commentdate) commentdate, GROUP_CONCAT(u2.username) commentauthorname, GROUP_CONCAT(c.anonim) commentanonym, GROUP_CONCAT(c.id) commentid, GROUP_CONCAT(u2.id) commentauthorid, GROUP_CONCAT(u2.usersurname) commentauthorsurname, GROUP_CONCAT(u2.usermainphoto) commentauthorphoto, GROUP_CONCAT(c.commentphotoid) commentphotoid
FROM posts p
LEFT JOIN comments c ON c.post = p.postid AND c.commentdel=0
LEFT JOIN users u ON u.id = p.postauthorid
LEFT JOIN users u2 ON u2.id = c.author
WHERE p.postwallid = :id AND p.postdel=0
GROUP BY postid
ORDER BY postid DESC
LIMIT 0, 3