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

这两个结果集怎样连起来
select dbo.JUSTRP_fordt(time_stamp,@groupby) as date,
staff_id,
sum(case state when 0 then 0 when 3 then 0 else timelong end) as loginl,
  sum(case state when 10 then timelong when 48 then timelong when 49 then timelong else 0 end) as busyl
from cc_vi_operatorlogex where time_stamp between @st and @et group by dbo.JUSTRP_fordt(time_stamp,@groupby),staff_id  

left join
(select staffid,
  sum(case csrlong when 0 then 0 else csrlong end) as totaltalklong,
sum(case type when 1 then( case csrlong when 0 then 0 else 1 end ) else 0 end)as incallnum,
  sum(case type when 2 then (case csrlong when 0 then 0 else csrlong end) else 0 end)as dialtalklong,
sum(case type when 2 then 1 else 0 end) as dialtalknum
from cc_vi_baseteledata where time_stamp between @st and @et group by dbo.JUSTRP_fordt(time_stamp,@groupby),staffid) as basedata

on cc_vi_operatorlogex.staff_id=basedata.staffid
------------------------------------------------------

报错 :left 附近有错误 ’as‘ 附近有错误 

要left join的 两个结果集单独写都有数据的

------解决方案--------------------
SQL code

SELECT basedata_1,*,basedata_2.* FROM (select dbo.JUSTRP_fordt(time_stamp,@groupby) as date,
staff_id,
sum(case state when 0 then 0 when 3 then 0 else timelong end) as loginl,
  sum(case state when 10 then timelong when 48 then timelong when 49 then timelong else 0 end) as busyl
from cc_vi_operatorlogex where time_stamp between @st and @et group by dbo.JUSTRP_fordt(time_stamp,@groupby),staff_id ) AS basedata_1
left join
(select staffid,
  sum(case csrlong when 0 then 0 else csrlong end) as totaltalklong,
sum(case type when 1 then( case csrlong when 0 then 0 else 1 end ) else 0 end)as incallnum,
  sum(case type when 2 then (case csrlong when 0 then 0 else csrlong end) else 0 end)as dialtalklong,
sum(case type when 2 then 1 else 0 end) as dialtalknum
from cc_vi_baseteledata where time_stamp between @st and @et group by dbo.JUSTRP_fordt(time_stamp,@groupby),staffid) as basedata_2

on basedata_1.staff_id=basedata_2.staffid

------解决方案--------------------
第一段结束不是表(别)名,不能使用left join
------解决方案--------------------
把前面一部分嵌套一下。