以下是我的sql查询:
`SELECT subject, threadpost.date, threadpost.idThreadPost as id,
threadcategories.category, users.userName, COUNT(idThreadSubs) AS subs, COUNT(idThreadReplies) as replies
FROM threadpost
JOIN threadcategories
ON idthreadcategories = threadpost.category
JOIN users
ON idUsers = UserId
LEFT JOIN threadsubs
ON threadpost.idThreadPost = threadsubs.ThreadId
LEFT JOIN threadreplies
ON threadpost.idThreadPost = threadreplies.ThreadId
WHERE idthreadcategories LIKE ?
GROUP BY idThreadPost
ORDER BY date desc
LIMIT 20;`
添加count(idthreadreplays)时会出现问题。如您所见,我正在按idthreadpost进行分组。这是因为我想检索线程的订阅计数和回复计数。
但是,结果给了我不正确的回复数(与订阅数相同)。
如何正确表述此查询?
1条答案
按热度按时间r6l8ljro1#
我想出来了。解决方案是在需要group by的联接中使用子查询: