多表分组统计的有关问题

多表分组统计的问题
表一: 
mainID subID
1 1
1 2
1 3
2 4
2 5
2 6

表二：
subID f1
1 1
1 2
2 6
3 5
3 2
3 7
........

我想实现按mainID实现对f1求和 


[解决办法]

SQL code

create table #A(mainID int,subID int)create table #B(subID int,f1 int)insert into #A select 1,1 union all select 1 ,2union allselect 1 ,3union allselect 2 ,4union allselect 2 ,5union allselect 2, 6insert into #B select 1, 1 union all select1 ,2union all select2, 6union all select3, 5union all select3, 2union all select3, 7select mainid,SUM(m.f1) from #A join (select subid,SUM(f1) as f1 from #b group by subid) m on #A.subid=m.subid group by mainid
[解决办法]
select mainID,SUM(fi) from 表一 t1,表二 t2 where t1.subID=t2.subID group by mainID
[解决办法]
SQL code
select mainid,SUM(f1) from #A join #B on #A.subID=#B.subID group by mainID
[解决办法]
SQL code
create table t1(mainID int,subID int)insert into t1 values(1 ,1)insert into t1 values(1 ,2)insert into t1 values(1 ,3)insert into t1 values(2 ,4)insert into t1 values(2 ,5)insert into t1 values(2 ,6)create table t2(subID int,f1 int)insert into t2 values(1 ,1)insert into t2 values(1 ,2)insert into t2 values(2 ,6)insert into t2 values(3 ,5)insert into t2 values(3 ,2)insert into t2 values(3 ,7)--如果不存在的不显示select m.mainID , sum(n.f1) f1 from t1 m, t2 n where m.subID = n.subID group by m.mainID /*mainID      f1          ----------- ----------- 1           23（所影响的行数为 1 行）*/--如果不存在的也要显示，且显示为0select m.mainID , isnull(sum(n.f1),0) f1 from t1 m left join t2 n on m.subID = n.subID group by m.mainID /*mainID      f1          ----------- ----------- 1           232           0（所影响的行数为 2 行）*/drop table t1 , t2
[解决办法]
SQL code
求记录条数create table #A(mainID int,subID int)create table #B(subID int,f1 int)insert into #A select 1,1 union all select 1 ,2union allselect 1 ,3union allselect 2 ,4union allselect 2 ,5union allselect 2, 6insert into #B select 1, 1 union all select1 ,2union all select2, 6union all select3, 5union all select3, 2union all select3, 7select mainid,count(f1) from #A left join #B on #A.subID=#B.subID group by mainID结果显示为：mainid    num1    62    0