select a.fno,a.ct1,b.ct2 from ( select F.fno,isnull(count(B.bno),0) as ct1 from forum F left join board B on F.fno = B.fno group by F.fno ) a, ( select F.fno,isnull(count(U.uno),0) as ct2 from forum F left join [user] U on F.fno = U.fno group by F.fno ) b where a.fno=b.fno
--> 测试数据: @forum declare @forum table (fno int) insert into @forum select 1 union all select 2 union all select 3 union all select 4--> 测试数据: @board declare @board table (bno int,fno int) insert into @board select 1,1 union all select 2,2 union all select 3,3 union all select 4,4 union all select 5,2--> 测试数据: @user declare @user table (uno int,fno int) insert into @user select 1,1 union all select 2,2 union all select 3,3 union all select 4,4 union all select 5,1select * , (select count(1) from @board where fno=a.fno) as ct1 , (select count(1) from @user where fno=a.fno) as ct2 from @forum a /* fno ct1 ct2 ----------- ----------- ----------- 1 1 2 2 2 1 3 1 1 4 1 1 */
select F.fno,F.name,F.url,F.pr,F.br,B.cou,U.cou,F.re from forum F left join (select fno,count(1) cou from board group by fno) B on F.fno = B.fno left join (select fno,count(1) cou from [user] group by fno) U on F.fno = U.fno
(
select F.fno,isnull(count(B.bno),0) as ct1
from forum F
left join board B
on F.fno = B.fno
group by F.fno
) a,
(
select F.fno,isnull(count(U.uno),0) as ct2
from forum F
left join [user] U
on F.fno = U.fno
group by F.fno
) b
where a.fno=b.fno
--> 测试数据: @forum
declare @forum table (fno int)
insert into @forum
select 1 union all
select 2 union all
select 3 union all
select 4--> 测试数据: @board
declare @board table (bno int,fno int)
insert into @board
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,2--> 测试数据: @user
declare @user table (uno int,fno int)
insert into @user
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,1select * ,
(select count(1) from @board where fno=a.fno) as ct1 ,
(select count(1) from @user where fno=a.fno) as ct2
from @forum a
/*
fno ct1 ct2
----------- ----------- -----------
1 1 2
2 2 1
3 1 1
4 1 1
*/
select F.fno,F.name,F.url,F.pr,F.br,B.cou,U.cou,F.re
from forum F
left join (select fno,count(1) cou from board group by fno) B on F.fno = B.fno
left join (select fno,count(1) cou from [user] group by fno) U on F.fno = U.fno