现在我有3个表manager,Actual,Projected
表结构分别是:
manager (manager_id,Manager_Name,Short_Name)
Actual (Manager_Name,Num_of_Deal,Assets,Sales_year,Revs,Year)
Projected(Manager_Name,Num_of_Deal,Assets,Sales_year,Revs,Year)
表内容:
-------------
manager
1 ACA
2 AL
3 ttt
4 fff
-------------
Actual
ACA 1 200 63379.00 11995.44 2007
AL 1 300 60010.00 6160.68 2007
---------------
Projected
ACA 1 333 66700.00 1377.26 2007
ACA 1 333 66700.00 5550.00 2008
---------------
我想要的结果是这样的:
Manager num_Proj Assets_Proj Sales_year_Proj Rev_Proj num_Actual Assets_Proj Sales_year_Proj Rev_Act Total_Rev year
ACA 1 333 66700.00 1377.26 1 200 63379.00 11995.44 13372.7 2007
AL 0 0 0 0 1 300 60010.00 6160.68 6160.68 2007
ttt 0 0 0 0 0 0 0 0 2007
fff 0 0 0 0 0 0 0 0 2007
其实就是想把Actual与Proj表连起来然后再算出同一年的Rev总和再把其他没有成绩的manager也附加上去
高手们帮忙吧 感谢感谢!!
------解决方案--------------------
也许不用sum
- SQL code
declare @manager table (manager_id int,Manager_Name varchar(20))
declare @Actual table (Manager_Name varchar(20),Num_of_Deal int,Assets int,Sales_year numeric(28,2),Revs numeric(28,2),[Year] int)
declare @Projected table (Manager_Name varchar(20),Num_of_Deal int,Assets int,Sales_year numeric(28,2),Revs numeric(28,2),[Year] int)
insert @manager select
1, 'ACA'
union all select
2, 'AL'
union all select
3, 'ttt'
union all select
4, 'fff'
insert @Actual select
'ACA', 1 , 200 , 63379.00 , 11995.44 , 2007
union all select
'AL' , 1 , 300 , 60010.00 , 6160.68 , 2007
---------------
insert @Projected select
'ACA', 1, 333, 66700.00 , 1377.26, 2007
union all select
'ACA', 1 , 333 , 66700.00 , 5550.00 , 2008
---------------
select
a.Manager_Name as Manager,
isnull(c.Num_of_Deal,0) as num_Proj,
isnull(c.Assets,0) as Assets_Proj,
isnull(c.Sales_year,0) as Sales_year_Proj,
isnull(c.Revs,0) as Rev_Proj,
isnull(b.Num_of_Deal,0) as num_Actual,
isnull(b.Assets,0) as Assets_Actual,
isnull(b.Sales_year,0) as Sales_year_Actual,
isnull(b.Revs,0) as Rev_Actual,
isnull(b.Revs,0)+isnull(c.Revs,0) as Total_Rev,
2007 as [Year]
from @manager a
left join @Actual b
on a.Manager_Name=b.Manager_Name and b.[Year]=2007
left join @Projected c
on a.Manager_Name=c.Manager_Name and c.[Year]=2007
--结果一样,略
------解决方案--------------------
- SQL code
create table manager(manager_id int,Manager_Name varchar(20))
create table Actual(Manager_Name varchar(20),Num_of_Deal int,Assets int,Sales_year numeric(28,2),Revs numeric(28,2),[Year] int)
create table Projected(Manager_Name varchar(20),Num_of_Deal int,Assets int,Sales_year numeric(28,2),Revs numeric(28,2),[Year] int)