select talk.talkid,TalkDetail,talktime,usernickname from dbo.talk,dbo.users where talk.userid=users.userid
select talk.talkid,count(talkcomment.tid) as counts from talk inner join talkcomment on talk.talkid = talkcomment.tid group by talk.talkid
两段代码,会产生两种结果,而我想要同时查询talk.talkid,TalkDetail,talktime,usernickname并且统计出talkcomment表中tid和talk表中talkid相同的一共有多少条,产生一个结果!谢谢
select talk.talkid,count(talkcomment.tid) as counts from talk inner join talkcomment on talk.talkid = talkcomment.tid group by talk.talkid
两段代码,会产生两种结果,而我想要同时查询talk.talkid,TalkDetail,talktime,usernickname并且统计出talkcomment表中tid和talk表中talkid相同的一共有多少条,产生一个结果!谢谢
SELECT a.talkid ,
a.TalkDetail ,
a.talktime ,
b.usernickname,
COUNT(c.tid) AS counts
FROM dbo.talk a
INNER JOIN dbo.users b
ON a.userid = b.userid
INNER JOIN talkcomment c
ON a.talkid = c.tid
GROUP BY a.talkid ,
a.TalkDetail ,
a.talktime ,
b.usernickname
from dbo.talk
inner join dbo.users on talk.userid=users.userid
inner join (select talkid,count(tid) as counts from talkcomment group by talkid)t
on talk.talkid=t.talkid
但是若现在a表中有三条数据,3个talkid分别为123,而c表中,对应的tid只有12的数据,没有3的数据,结果3会查不出来,应该让talkid=3的也查出来,后面的counts=0,该怎样修改?
但是若现在a表中有三条数据,3个talkid分别为123,而c表中,对应的tid只有12的数据,没有3的数据,结果3会查不出来,应该让talkid=3的也查出来,后面的counts=0,该怎样修改?
--改成LEFT JOIN即可
SELECT a.talkid ,
a.TalkDetail ,
a.talktime ,
b.usernickname,
ISNULL(COUNT(c.tid),0) AS counts
FROM dbo.talk a
INNER JOIN dbo.users b
ON a.userid = b.userid
LEFT JOIN talkcomment c
ON a.talkid = c.tid
GROUP BY a.talkid ,
a.TalkDetail ,
a.talktime ,
b.usernickname