none
3个表的联合查询和统计 RRS feed

  • 问题

  • 我有3个表,一个是帖子表 ,一个是帖子回复表,一个是帖子关注表。回复表,关注表和帖子表有外键关系。我想查询出所有帖子的信息,并且显示出统计每条帖子的回复数和关注数。SQL语句该怎么写呢?
    2011年8月12日 6:02

答案

  • Shadow兄動作真快,我正要貼上來就發現已經回了,不過我還是把我的作法提供給發文者參考。

    --帖子表
    declare @post table
    (
     PostId int
    ,Sub nvarchar(50) 
    ,Body nvarchar(50)
    ,UserName varchar(50)
    ) 
    --帖子回复表
    declare @post_detail table
    (
     PostId int
     ,Msg nvarchar(50)
     ,UserName varchar(50)
     )
    --帖子关注表
    declare @post_alert table
    (
    	PostId int
    	,UserName varchar(50)	
    ) 
    
    --測試資料
    insert into @post values(1,'Subject1','Body1','User1'),(2,'Subject2','Body2','User2')
    insert into @post_detail values(1,'Message1','UserA'),(1,'Message2','UserB'),(2,'Message1','UserC')
    insert into @post_alert values(1,'User1'),(1,'UserB'),(2,'User1'),(2,'User2'),(2,'UserC')
    
    --统计每条帖子的回复数和关注数
    select a.PostId,a.Sub,a.Body
    ,(select COUNT(PostId) from @post_detail b
     where b.PostId = a.PostId) as 回复数
    ,(select COUNT(PostId) from @post_alert c
     where c.PostId = a.PostId) as 关注数
    from @post a
    
    



    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年8月12日 6:44

全部回复

  • use tempdb
    Go
    
    Create table 帖子表
    (
     帖子id int identity primary key,
     帖子名 varchar(50)
    
    )
    Go
    Insert into 帖子表 values ('帖子1'),('帖子2'),('帖子3')
    
    
    Create table 帖子回复表
    (
     帖子回复id int identity primary key,
     帖子id int foreign key references 帖子表(帖子id),
     回复数 int
    
    
    )
    Go
    Insert into 帖子回复表 values (1,1),(2,2),(3,3)
    
    Create table 帖子关注表
    (
     帖子关注id int identity primary key,
     帖子id int foreign key references 帖子表(帖子id),
     关注数 int 
    )
    Go
    Insert into 帖子关注表 values (1,100),(2,200),(3,300)
    
    Select a.帖子名,b.回复数,c.关注数 from 帖子表 a Inner join 帖子回复表 b on a.帖子id=b.帖子id
    Inner join 帖子关注表 c on a.帖子id=c.帖子id
    


    結果:

     


    Shadowと愉快なコード達
    2011年8月12日 6:38
  • Shadow兄動作真快,我正要貼上來就發現已經回了,不過我還是把我的作法提供給發文者參考。

    --帖子表
    declare @post table
    (
     PostId int
    ,Sub nvarchar(50) 
    ,Body nvarchar(50)
    ,UserName varchar(50)
    ) 
    --帖子回复表
    declare @post_detail table
    (
     PostId int
     ,Msg nvarchar(50)
     ,UserName varchar(50)
     )
    --帖子关注表
    declare @post_alert table
    (
    	PostId int
    	,UserName varchar(50)	
    ) 
    
    --測試資料
    insert into @post values(1,'Subject1','Body1','User1'),(2,'Subject2','Body2','User2')
    insert into @post_detail values(1,'Message1','UserA'),(1,'Message2','UserB'),(2,'Message1','UserC')
    insert into @post_alert values(1,'User1'),(1,'UserB'),(2,'User1'),(2,'User2'),(2,'UserC')
    
    --统计每条帖子的回复数和关注数
    select a.PostId,a.Sub,a.Body
    ,(select COUNT(PostId) from @post_detail b
     where b.PostId = a.PostId) as 回复数
    ,(select COUNT(PostId) from @post_alert c
     where c.PostId = a.PostId) as 关注数
    from @post a
    
    



    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年8月12日 6:44
  • 谢谢你的帮助。表设计的时候每个表里没有那个关注数,或回复数。
    2011年8月12日 7:41
  • 这个SQL语句如果在一个表里没有数据的时候,另一个统计出来的结果会为0.怎么才能避免这个问题呢?

    2011年8月12日 7:42
  • 你好,

    如果使用INNER JOIN连接,只要有一个表没记录,结果就不会产生记录。你可以使用LEFT JOIN, RIGHT JOIN 显示某些表存在数据库的记录。以Shadow的代码为例,如果删除最后一个表的记录,把最后的SELECT语句改成LEFT JOIN, 你还是可以得到3条记录,只不过“关注数“字段是NULL。关于LEFT JOIN和RIGHT JOIN的用法,请参阅:Using Outer Joins


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    2011年8月12日 9:08
  • 这个SQL语句如果在一个表里没有数据的时候,另一个统计出来的结果会为0.怎么才能避免这个问题呢?


    以Terry大的代码为例,只要弄成sub query再判断就行了

    --帖子表
    declare @post table
    (
     PostId int
    ,Sub nvarchar(50) 
    ,Body nvarchar(50)
    ,UserName varchar(50)
    ) 
    --帖子回复表
    declare @post_detail table
    (
     PostId int
     ,Msg nvarchar(50)
     ,UserName varchar(50)
     )
    --帖子关注表
    declare @post_alert table
    (
    	PostId int
    	,UserName varchar(50)	
    ) 
    
    --測試資料
    insert into @post values(1,'Subject1','Body1','User1'),(2,'Subject2','Body2','User2'),(3,'Subject3','Body3','User3')
    insert into @post_detail values(1,'Message1','UserA'),(1,'Message2','UserB'),(2,'Message1','UserC')
    insert into @post_alert values(1,'User1'),(1,'UserB'),(2,'User1'),(2,'User2'),(2,'UserC')
    
    --统计每条帖子的回复数和关注数
    
    Select * from 
    (
    select a.PostId,a.Sub,a.Body
    ,(select COUNT(PostId) from @post_detail b
     where b.PostId = a.PostId) as 回复数
    ,(select COUNT(PostId) from @post_alert c
     where c.PostId = a.PostId) as 关注数
    from @post a
    ) subTable
    Where subTable.回复数<>0 And subTable.关注数<>0
    
    
    
    

    結果:

     


    Shadowと愉快なコード達
    2011年8月12日 10:08
  • 除了Shadow兄所說的子查詢,也可以用exists做過濾。

    select a.PostId,a.Sub,a.Body
    ,(select COUNT(PostId) from @post_detail b
     where b.PostId = a.PostId) as 回复数
    ,(select COUNT(PostId) from @post_alert c
     where c.PostId = a.PostId) as 关注数
    from @post a
    where exists (select Postid from @post_detail b where a.PostId = b.PostId)
    and exists (select Postid from @post_alert b where a.PostId = b.PostId)
    



    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年8月12日 10:20
  • 这个SQL语句如果在一个表里没有数据的时候,另一个统计出来的结果会为0.怎么才能避免这个问题呢?


    以Terry大的代码为例,只要弄成sub query再判断就行了

    --帖子表
    declare @post table
    (
     PostId int
    ,Sub nvarchar(50) 
    ,Body nvarchar(50)
    ,UserName varchar(50)
    ) 
    --帖子回复表
    declare @post_detail table
    (
     PostId int
     ,Msg nvarchar(50)
     ,UserName varchar(50)
     )
    --帖子关注表
    declare @post_alert table
    (
    	PostId int
    	,UserName varchar(50)	
    ) 
    
    --測試資料
    insert into @post values(1,'Subject1','Body1','User1'),(2,'Subject2','Body2','User2'),(3,'Subject3','Body3','User3')
    insert into @post_detail values(1,'Message1','UserA'),(1,'Message2','UserB'),(2,'Message1','UserC')
    insert into @post_alert values(1,'User1'),(1,'UserB'),(2,'User1'),(2,'User2'),(2,'UserC')
    
    --统计每条帖子的回复数和关注数
    
    Select * from 
    (
    select a.PostId,a.Sub,a.Body
    ,(select COUNT(PostId) from @post_detail b
     where b.PostId = a.PostId) as 回复数
    ,(select COUNT(PostId) from @post_alert c
     where c.PostId = a.PostId) as 关注数
    from @post a
    ) subTable
    Where subTable.回复数<>0 And subTable.关注数<>0
    
    
    
    

    結果:

     


    Shadowと愉快なコード達
    谢谢你的回复,如果加上where条件,那个当回复表或关注表里没有数据的时候,帖子表的信息也就无法获取到了。
    2011年8月12日 10:22
  • 谢谢版主,另外2个表的数据是统计出来的。表就像Terry 老大的那样。
    2011年8月12日 10:26
  • 您是要这样的效果?

    --帖子表
    declare @post table
    (
     PostId int
    ,Sub nvarchar(50) 
    ,Body nvarchar(50)
    ,UserName varchar(50)
    ) 
    --帖子回复表
    declare @post_detail table
    (
     PostId int
     ,Msg nvarchar(50)
     ,UserName varchar(50)
     )
    --帖子关注表
    declare @post_alert table
    (
    	PostId int
    	,UserName varchar(50)	
    ) 
    
    --測試資料
    insert into @post values(1,'Subject1','Body1','User1'),(2,'Subject2','Body2','User2'),(3,'Subject3','Body3','User3')
    insert into @post_detail values(1,'Message1','UserA'),(1,'Message2','UserB'),(2,'Message1','UserC')
    insert into @post_alert values(1,'User1'),(1,'UserB'),(2,'User1'),(2,'User2'),(2,'UserC')
    
    --统计每条帖子的回复数和关注数
    
    
    select a.PostId,a.Sub,a.Body
    ,Case (SELECT COUNT(PostId) from @post_detail b
     where b.PostId = a.PostId) When 0 Then NULL Else (SELECT COUNT(PostId) from @post_detail b
     where b.PostId = a.PostId) End As 回复数
    ,Case (SELECT COUNT(PostId) from @post_alert c
     where c.PostId = a.PostId) When 0 Then NULL Else (SELECT COUNT(PostId) from @post_alert c
     where c.PostId = a.PostId) End As 关注数
    from @post a
    

     


    Shadowと愉快なコード達
    • 已建议为答案 TerryChuang 2011年8月12日 13:36
    2011年8月12日 10:32
  • 恩。可以了。谢谢了。谢谢大家了。
    2011年8月12日 11:51
  • when 0 then 0

    这样感觉意义不大耶

    我以為是when 0 then NULL


    Shadowと愉快なコード達
    2011年8月12日 13:01
  • 恩。谢谢。我有看了看我写的,昨天条件写错了。还以为Terry老大的有问题。抱歉。谢谢你的帮助。
    2011年8月13日 8:15