Question

sql语句统计每条信息的回复条数

如：我有两个表一个留言表Message(Mid,Mtitle,Mtime)表，一个回复表Respon(Rid,Mid,Rtime)，我想统计一下每条留言的回复条数，没显示到界面上，请问Sql语句要怎么写，我要取出的字段有Mtitle，Mtime，回复数，各位大侠帮帮忙！最好能给写个例子。谢啦！！！

Answer 1

select m.Mtitle as Mtitle ,m.Mtime as Mtime ,count(*) as count from Message m,Respon r where r.Mid = M.Mid group by m.Mid
------------------
希望有用。
-------------------如果要把0回复得也显示出来~就是---------------------------
select m.Mtitle as Mtitle ,m.Mtime as Mtime ,count(r.Rid) as count from Message m left join Respon r on r.Mid = M.Mid group by m.Mid

Answer 2

只显示有回复的Message：
select m.Mtitle,m.Mtime,count(r.rid) as rcount
from Message m
inner join Respon r on m.mid = r.rid
group by m.Mtitle,m.Mtime

所有的Message：
select m.Mtitle,m.Mtime,count(r.rid) as rcount
from Message m
left join Respon r on m.mid = r.rid
group by m.Mtitle,m.Mtime

Answer 3

要是不考虑性能的情况下，就用二楼的sql就可以了，否则，我建议，在Message增加回复数字段，在每次有人回复的时候，给相应主题回复数加1。 这是在大数据表的情况普遍采取的策略。

Answer 4

select mtitle,mtime,count(1) 回复数 from Message a,Respon b group by mtitle,mtime where a.mid=b.mid;

Answer 5

select Mtitle, Mtime, count(Mid) as 回复数 from Message
left outer join Respon
on Message.Mid = Respon.Mid
group by Mid, Mtitle, Mtime