表A
--------------------
A日期 A电话费
--------------------
2007-4-11 5000
2007-4-11 5000
2007-4-11 5000
--------------------
表B
--------------------
B日期 B手机费
--------------------
2007-4-11 1000
2007-4-11 1000
2007-4-12 1000
2007-4-12 1000
--------------------
得到结果
日期 电话费 手机费
2007-4-11 25000 2000
2007-4-12 0 2000
对表B 进行分组查询 同时查询表A 日期相同的总和
------解决方案--------------------------------------------------------
select 日期,sum(case type when 1 then 电话费 else 0 end ) as 电话费,sum(case type when 2 then 电话费 else 0 end ) as 手机费from(select A日期 as 日期 ,A电话费 as 电话费 ,1 as Type union Select B日期 as 日期 ,B手机费 as 电话费 ,2 as Type ) AS AAGROUP BY 日期
------解决方案--------------------------------------------------------
也寫一種方法
Select
IsNull(A.A日期, B日期) As 日期,
IsNull(A.A电话费, 0) As 电话费,
IsNull(B.B手机费, 0) As 手机费
From
(Select A日期, SUM(A电话费) As A电话费 From A Group By A日期) A
Full Join
(Select B日期, SUM(B手机费) As B手机费 From B Group By B日期) B
On A.A日期 = B.B日期