select messageid,(select count(*) from tablename where topicid =a.topicid ) as [count],title from tablename a where topicid=messageid
select message,count(*),title from text order by message,title
select *,0 as count into #t from table where 1=2 select * into #t2 from table where 1=2 insert into #t select * from table where messageid=topicid insert into #t2 select topicid,count(topicid)-1 as count from table group by topicid update #t set count=#t2.count from #t2 where #t2.topicid=#t.topicid select messageid,count,title from #t drop table #t drop table #t2
select messageid ,(select count(*) from testwhere messageid =a.topicid ) as counts,title from test as a where messageid=topicid
走了一个弯路,这样就行了. select topicid as messageid,count(topicid)-1 as count,title from table group by topcid,title
where topicid=messageid
from text
order by message,title
select * into #t2 from table where 1=2
insert into #t select * from table where messageid=topicid
insert into #t2 select topicid,count(topicid)-1 as count from table group by topicid
update #t
set count=#t2.count
from #t2
where #t2.topicid=#t.topicid
select messageid,count,title from #t
drop table #t
drop table #t2
select topicid as messageid,count(topicid)-1 as count,title from table group by topcid,title