日期:2014-05-18  浏览次数:20500 次

多表分组统计的问题
表一: 
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 all
select 1 ,3union all
select 2 ,4union all
select 2 ,5union all
select 2, 6
insert into #B select 1, 1 union all select
1 ,2union all select
2, 6union all select
3, 5union all select
3, 2union all select
3, 7
select 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 行)
*/

--如果不存在的也要显示,且显示为0
select 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           23
2           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 all
select 1 ,3union all
select 2 ,4union all
select 2 ,5union all
select 2, 6
insert into #B select 1, 1 union all select
1 ,2union all select
2, 6union all select
3, 5union all select
3, 2union all select
3, 7
select mainid,count(f1) from #A left join #B on #A.subID=#B.subID group by mainID
结果显示为:
mainid    num
1    6
2    0