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 busylfrom cc_vi_operatorlogex where time_stamp between @st and @et group by dbo.JUSTRP_fordt(time_stamp,@groupby),staff_id ) AS basedata_1left 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 dialtalknumfrom cc_vi_baseteledata where time_stamp between @st and @et group by dbo.JUSTRP_fordt(time_stamp,@groupby),staffid) as basedata_2on basedata_1.staff_id=basedata_2.staffid
------解决方案--------------------
第一段结束不是表(别)名,不能使用left join
------解决方案--------------------
把前面一部分嵌套一下。