所以我想从"board_b"中选择*,这是回复最多的线程。我的问题是,这些答复实际上在同一张表中。看看这个:
+---+-----------+---------+
|ID | name | replyto |
+---+-----------+---------+
| 1 | newthread | |
| 2 | reply | 1 |
+---+-----------+---------+
(注意:name列没有设置为这些,只是为了演示)如您所见,1
是一个新线程,2
是对1
的回复。现在我有一个满是这些的表,这个表有更多的列(文本、时间戳等),但总体思路与上面的一样。
我想要实现的是选择所有线程,并根据大多数回复对它们进行排序(还限制为0,20)。我试过查找连接表,但它太复杂了,我无法理解,所以一个示例代码会很好。
这样的东西可以做到:
SELECT board.id, board.name, COUNT(reply.id)
FROM board_b board INNER JOIN board_b reply ON board.id = reply.replyto
GROUP BY board.id, board.name
ORDER BY COUNT(reply.id) desc
LIMIT 20
您想要使用group by
:
select replyto as thread, count(*) as cnt
from board_b
group by replyto
order by cnt desc
limit 0, 20;
select c.replyto, c.replycount
from
(
select a.replyto as replyto, count(*) as replycount
from board_b a
inner join (
select id, name, replyto
from board_b
where replyto is null
) b
on b.id = a.replyto
group by a.replyto
) c
where c.replycount between 0 and 20
order by c.replycount desc