mysql group by with multi join多连接

0sgqnhkj 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(291)

以下是我的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进行分组。这是因为我想检索线程的订阅计数和回复计数。
但是，结果给了我不正确的回复数（与订阅数相同）。
如何正确表述此查询？

mysql

来源：https://stackoverflow.com/questions/51127108/mysql-group-by-with-multi-join

1条答案

按热度按时间

r6l8ljro1#

我想出来了。解决方案是在需要group by的联接中使用子查询：

`SELECT subject, threadpost.date, threadpost.idThreadPost as id,
    threadcategories.category, users.userName, tsubs.subs AS subs, trep.replies as replies
    FROM threadpost
      JOIN threadcategories
        ON idthreadcategories = threadpost.category
      JOIN users
        ON idUsers = UserId
      LEFT JOIN (
        SELECT threadsubs.ThreadId as tsubId, COUNT(idThreadSubs) as subs
        FROM threadsubs
        GROUP BY idThreadSubs
      ) as tsubs
        ON tsubs.tsubId = threadpost.idThreadPost
      LEFT JOIN (
        SELECT threadreplies.ThreadId as tId, COUNT(threadreplies.idThreadReplies) as replies
        FROM threadreplies
        GROUP BY threadreplies.ThreadId
      ) AS trep
        ON trep.tId = threadpost.idThreadPost
    WHERE idthreadcategories LIKE ?
    ORDER BY date desc
    LIMIT 20;`

赞(0）回复(0）举报 2021-06-20

我来回答

mysql group by with multi join多连接

1条答案

相关问题

热门标签

最新问答