--或 select a.id,a.title,a.content,a.writename,回复数=count(id) from T_s a,T_s b where a.id=b.par_id and a.id=0 group by a.id,a.title,a.content,a.writename
--表设计有点不合理,应该将主题与回复分开select a.*,b.回复数 from( select id,title,content,writeName from T_s where par_id=0 group by id,title,content,writeName )a join( select par_id,回复数=count(*) from T_s where par_id<>0 group by par_id )b on a.id=b.par_id
--try select a.id,a.title,a.content,a.writename,回复数=sum(case when b.id is null then 0 else 1 end) from T_s a left join T_s b on a.id=b.par_id and a.id=0 group by a.id,a.title,a.content,a.writename
好了,写出来了,谢谢wzh1215(懒猫)大哥的提醒! select id,tietu,title,backcount=(select count(*) from T_book b where b.par_id=a.id),hits,username,shijian from T_book a where par_id=0 好交差了
select a.id,a.title,a.content,a.writename,回复数=count(id) from T_s a,T_s b
where a.id=b.par_id and a.id=0
group by a.id,a.title,a.content,a.writename
from(
select id,title,content,writeName
from T_s
where par_id=0
group by id,title,content,writeName
)a join(
select par_id,回复数=count(*)
from T_s
where par_id<>0
group by par_id
)b on a.id=b.par_id
判断条件有问题!
select a.id,a.title,a.content,a.writename,回复数=sum(case when b.id is null then 0 else 1 end) from T_s a left join T_s b on a.id=b.par_id and a.id=0
group by a.id,a.title,a.content,a.writename
select id,tietu,title,backcount=(select count(*) from T_book b where b.par_id=a.id),hits,username,shijian from T_book a where par_id=0
好交差了