table1
company model inAmount
Nokia N95 3
Nokia N72 2
Nokia N73 4
Nokia N95 2
Nokia N72 5
MOTO 3580 3
MOTO 3591 6
MOTO 3580 2
table2
Company model storeAmount
Nokia N95 2
Nokia N72 10
Nokia N72 1
MOTO 3580 7
Nokia N70 6
KONKA T31 15
table3
Company model outAmount
Nokia N95 5
Nokia N95 3
Nokia N95 1
Nokia N70 4
Nokia N81 3
我希望的查询的结果是:
ResultCompany model inTotal StoreTotal outTotal
Nokia N95 5 2 9
Nokia N72 7 11 0
Nokia N73 4 0 0
Nokia N70 0 6 4
Nokia N81 0 0 3
MOTO 3580 5 7 0
MOTO 3591 6 0 0
KONKA T31 0 15 0说明:
三个表均要按company,model分类汇总。
company model inAmount
Nokia N95 3
Nokia N72 2
Nokia N73 4
Nokia N95 2
Nokia N72 5
MOTO 3580 3
MOTO 3591 6
MOTO 3580 2
table2
Company model storeAmount
Nokia N95 2
Nokia N72 10
Nokia N72 1
MOTO 3580 7
Nokia N70 6
KONKA T31 15
table3
Company model outAmount
Nokia N95 5
Nokia N95 3
Nokia N95 1
Nokia N70 4
Nokia N81 3
我希望的查询的结果是:
ResultCompany model inTotal StoreTotal outTotal
Nokia N95 5 2 9
Nokia N72 7 11 0
Nokia N73 4 0 0
Nokia N70 0 6 4
Nokia N81 0 0 3
MOTO 3580 5 7 0
MOTO 3591 6 0 0
KONKA T31 0 15 0说明:
三个表均要按company,model分类汇总。
model,
sum(inAmount) as intotal,
sum(storeAmount) as storetotal,
sum(outAmount) as outtotal
from (
select company,model,inAmount,0,0 from tb1 union all
select company,model,0,storeAmount,0 from tb2 union all
select company,model,0,0,outAmount from tb3)
t
group by company,model
model,
sum(inAmount) as intotal,
sum(storeAmount) as storetotal,
sum(outAmount) as outtotal
from (
select company,model,inAmount,0 as storeAmount,0 as outAmount from tb1 union all
select company,model,0,storeAmount,0 from tb2 union all
select company,model,0,0,outAmount from tb3)
t
group by company,model
from
(
select sum(inAmount) as inTotal 0 as StoreTotal, 0 as outTotal , company,model
from table1
group by company , modelunion allselect 0 as inTotal , sum(storeAmount ) as StoreTotal, 0 as outTotal , company,model
from table 2
group by company , modelunion allselect 0 as inTotal ,0 as StoreTotal, sum(outAmount ) as outTotal , company,model
from table3
group by company , model
)
group by company , model