表forum
字段fno
1
2
3
4
表board
字段bno，字段fno
1 1
2 2
3 3
4 4
5 2
表user
字段uno，字段fno
1 1
2 2
3 3
4 4
5 1
3个表都是与字段fno关联，表board与表user计算总数ct1和ct2，得到的结果是：
fno name url pr br ct1 ct2 remark
1 1 2
2 2 1
3 1 1
4 1 1
这是我写的sql可是有错误：
sql_str = "select F.fno,F.name,F.url,F.pr,F.br,isnull(count(B.bno),0) as ct1,isnull(count(U.uno),0) as ct2,F.remark from forum F left outer join board B on F.fno = B.fno left outer join [user] U on F.fno = U.fno group by F.fno,F.name,F.url,F.pr,F.br,F.remark order by F.fno desc";
我得到的错误结果是：
fno name url pr br ct1 ct2 remark
1 2 2
2 2 2
3 1 1
4 1 1
求正确的写法。
------解决方案--------------------
- SQL code
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 ) bwhere a.fno=b.fno
------解决方案--------------------
- SQL code
--> 测试数据: @forumdeclare @forum table (fno int)insert into @forumselect 1 union allselect 2 union allselect 3 union allselect 4--> 测试数据: @boarddeclare @board table (bno int,fno int)insert into @boardselect 1,1 union allselect 2,2 union allselect 3,3 union allselect 4,4 union allselect 5,2--> 测试数据: @userdeclare @user table (uno int,fno int)insert into @userselect 1,1 union allselect 2,2 union allselect 3,3 union allselect 4,4 union allselect 5,1select * ,(select count(1) from @board where fno=a.fno) as ct1 ,(select count(1) from @user where fno=a.fno) as ct2from @forum a/*fno ct1 ct2----------- ----------- -----------1 1 22 2 13 1 14 1 1*/
------解决方案--------------------
- SQL code
select F.fno,F.name,F.url,F.pr,F.br,B.cou,U.cou,F.remarkfrom forum Fleft join (select fno,count(1) cou from board group by fno) B on F.fno = B.fnoleft join (select fno,count(1) cou from [user] group by fno) U on F.fno = U.fno