我想一句话是不能出来的。需要用一段语句了 假设主表是tb1 回帖是tb2 select [fid],sum(*)as sum1 into tab_temp from tb2 group by fid insert into tab_temp select id,0 from tb1 where not exists(select * from tab_temp where tab_temp.fid=tb1.id) select [id],[date],sum1 from tab_temp,tb1 where tb1.[id]=tab_temp.fid
用视图做,很简单的 SELECT dbo.主贴.id, 主贴_1.标题, 主贴_1.时间, COUNT(dbo.回贴.id) AS 回贴数 FROM dbo.主贴 INNER JOIN dbo.主贴 主贴_1 ON dbo.主贴.id = 主贴_1.id LEFT OUTER JOIN dbo.回贴 ON dbo.主贴.id = dbo.回贴.id GROUP BY dbo.主贴.id, dbo.主贴.id, 主贴_1.标题, 主贴_1.时间
对了,如果要加上条件的话SELECT dbo.主贴.id, 主贴_1.标题, 主贴_1.时间, COUNT(dbo.回贴.id) AS 回贴数 FROM dbo.主贴 INNER JOIN dbo.主贴 主贴_1 ON dbo.主贴.id = 主贴_1.id LEFT OUTER JOIN dbo.回贴 ON dbo.主贴.id = dbo.回贴.id WHERE (dbo.主贴.id = 你要查的id值) GROUP BY dbo.主贴.id, dbo.主贴.id, 主贴_1.标题, 主贴_1.时间
select a.标题,a.时间,b.ht as 回贴数 from 主贴表 a left join (select id,sum(回贴) as ht from 回贴表 group by id) b on a.id=b.id
From Tb_reply,Tb_zhutie
where Tb_reply.id=Tb_zhutie.id
group by tb_zhutie.id
from tbl_MailTopic a,tbl_childTopic b
where a.ipid=b.ipid
group by ipid,a.ctitle,a.cReg_date
from tbl_MailTopic a left join tbl_childTopic b
on a.ipid=b.ipid
group by ipid
from tbl_MainTopic a ,tbl_ChildTopic b
where a.iPid=b.iPid AND iPid=你需要的直
group by aa.cTitle,a.cReg_Date
select a.cTitle,a.cReg_Date ,sum(1) as 回贴数
from tbl_MainTopic a ,tbl_ChildTopic b
where a.iPid=b.iPid AND iPid=你需要的直
group by a.cTitle,a.cReg_Date
假设主表是tb1 回帖是tb2
select [fid],sum(*)as sum1 into tab_temp from tb2 group by fid
insert into tab_temp select id,0 from tb1 where not exists(select * from tab_temp where tab_temp.fid=tb1.id)
select [id],[date],sum1 from tab_temp,tb1 where tb1.[id]=tab_temp.fid
SELECT dbo.主贴.id, 主贴_1.标题, 主贴_1.时间, COUNT(dbo.回贴.id) AS 回贴数
FROM dbo.主贴 INNER JOIN
dbo.主贴 主贴_1 ON dbo.主贴.id = 主贴_1.id LEFT OUTER JOIN
dbo.回贴 ON dbo.主贴.id = dbo.回贴.id
GROUP BY dbo.主贴.id, dbo.主贴.id, 主贴_1.标题, 主贴_1.时间
FROM dbo.主贴 INNER JOIN
dbo.主贴 主贴_1 ON dbo.主贴.id = 主贴_1.id LEFT OUTER JOIN
dbo.回贴 ON dbo.主贴.id = dbo.回贴.id
WHERE (dbo.主贴.id = 你要查的id值)
GROUP BY dbo.主贴.id, dbo.主贴.id, 主贴_1.标题, 主贴_1.时间
from 主贴表 a left join (select id,sum(回贴) as ht from 回贴表 group by id) b on a.id=b.id