我正在使用PHP和MYSQL开发一个基本的社交网络。我试图在一张表中显示4个最受欢迎的帖子。
我有两个表,表1是'discussions'
,表2是'comments'
。表'discussions'
有一列"id
",它与表'comments'
中的列'discid'
相匹配。因此,每当用户对讨论发表评论时,都会在表"comments"中添加一行,'discid'
是讨论的id。我可以显示最近的评论和讨论,但我无法显示最受欢迎的讨论,所以评论最多的讨论。
因此,我需要找到一种方法,根据某个讨论id在表'comments'
的列'discid'
中出现的次数对我的表讨论进行排序。
例如:
On the discussion with id 7, there are 4 comments with discid 1.
On the discussion with id 2, there are 3 comments with discid 2.
所以我需要我的sql查询首先显示id 7,然后显示id 2,。。。
有谁能帮我吗?非常感谢!
让我知道它是否有效。。
SELECT d.*
FROM discussions AS d
JOIN ( SELECT c.*, COUNT(c.discid) AS cnt
FROM comments AS c
GROUP BY c.discid
) AS c2 ON ( c2.discid = d.id )
ORDER BY c2.cnt DESC;
SELECT discid AS Discussion_ID, COUNT(*) AS occurences
FROM comments
GROUP BY discid
ORDER BY occurences DESC
LIMIT 4
希望这能有所帮助。
我会尝试类似的东西
SELECT discid,COUNT(*) AS cnt FROM comments GROUP BY discid ORDER BY cnt DESC LIMIT 5;
这将为您提供一个discid-s列表和名为"cnt"的评论计数,按计数降序排列。