sql 连接和计数

发布于 2024-12-25 05:10:33 字数 280 浏览 1 评论 0原文

假设我有这个查询，

 SELECT ft.*, m.*
   FROM forum_topics ft
     INNER JOIN members m ON ft.author = m.id
     WHERE ft.forum =  '$forum'
     ORDER BY ft.lastpost DESC

我还想从表 forum_replies 中获取行计数，其中 id = ft.id。我怎么能这么做呢？

原文

Say I have this query

 SELECT ft.*, m.*
   FROM forum_topics ft
     INNER JOIN members m ON ft.author = m.id
     WHERE ft.forum =  '$forum'
     ORDER BY ft.lastpost DESC

I want to also get a row count in that from the table forum_replies where the id = ft.id.
How could I do that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天赋异禀 2025-01-01 05:10:33

根据您的数据库实现，这可能有效：

 SELECT ft.*, 
        m.*,
        ( SELECT count(1)  
            FROM forum_replies fr 
           WHERE fr.id = ft.id) AS nr_of_replies 
   FROM forum_topics ft
     INNER JOIN members m ON ft.author = m.id
     WHERE ft.forum =  '$forum'
     ORDER BY ft.lastpost DESC

HTH

Depending on your DB implementation this might work:

 SELECT ft.*, 
        m.*,
        ( SELECT count(1)  
            FROM forum_replies fr 
           WHERE fr.id = ft.id) AS nr_of_replies 
   FROM forum_topics ft
     INNER JOIN members m ON ft.author = m.id
     WHERE ft.forum =  '$forum'
     ORDER BY ft.lastpost DESC

HTH

回复收藏 0 原文

江心雾 2025-01-01 05:10:33

SELECT ft.*, m.*, fr.ReplyCount
FROM forum_topics ft
INNER JOIN members m ON ft.author = m.id
inner join (
   Select Id, Count(*) as ReplyCount
   from forum_replys 
   group by id
 ) as fr 
 on ft.Id = fr.Id
 WHERE ft.forum =  '$forum'
 ORDER BY ft.lastpost DESC

SELECT ft.*, m.*, fr.ReplyCount
FROM forum_topics ft
INNER JOIN members m ON ft.author = m.id
inner join (
   Select Id, Count(*) as ReplyCount
   from forum_replys 
   group by id
 ) as fr 
 on ft.Id = fr.Id
 WHERE ft.forum =  '$forum'
 ORDER BY ft.lastpost DESC

回复收藏 0 原文

~没有更多了~