sql语句优化问题

[ayun00]

  ;with sub as
  (
  select Business_Type, DepartmentID, COUNT(1) dbcount from [db_owner].[WWF_INSTANCE]  
   left join  [db_owner].[Arc_Archive] on  [db_owner].[Arc_Archive].userid=  [db_owner].[WWF_INSTANCE] .[Business_Field1]
   group by [db_owner].[WWF_INSTANCE].Business_Type,[db_owner].[Arc_Archive].DepartmentID
   )
   select DepartmentID
,(select sum(dbcount) from sub where Business_Type ='1' and sub.DepartmentID=[db_owner].[Arc_Archive].DepartmentID) 
,(select sum(dbcount) from sub where Business_Type ='2' and sub.DepartmentID=[db_owner].[Arc_Archive].DepartmentID) 
   from [db_owner].[Arc_Archive]  group by DepartmentID

目的是从WWF_INSTANCE找出 Business_Type 不同的 记录数, 并按 DepartmentID 分组
DepartmentID 本身不在 WWF_INSTANCE表中, 要去Arc_Archive 表查询
最终的结果希望是这样
DepartmentID Business_Type=1 Business_TypeCount Business_Type=2 Business_TypeCount ................
1 1 10 2 20
1 1 15 2 40

[RINK_1]

以下试试，

select DepartmentID, 
sum(case when Business_Type='1' then 1 else 0 end) as type_1, 
sum(case when Business_Type='2' then 1 else 0 end) as type_2
from [db_owner].[WWF_INSTANCE]  
left join  [db_owner].[Arc_Archive] on  [db_owner].[Arc_Archive].userid=  [db_owner].[WWF_INSTANCE] .[Business_Field1]
group by [db_owner].[Arc_Archive].DepartmentID