好像是提示(
select agent_id,订单数=count(*)
from table3 c join table1 a on c.agent_id=a.agent_id group by a.agent_id
) a
附近的错误
select agent_id,订单数=count(*)
from table3 c join table1 a on c.agent_id=a.agent_id group by a.agent_id
) a
附近的错误
select
a.agent_id as 工号,
a.name as 姓名,
count(b.id) as 订单数,
sum(case when c.type='报号数' then 1 else 0 end) as 咨询数,
sum(case when c.type='报号数' then 0 else 1 end) as 报号数,
(case when count(b.id) is null then 0 else count(b.id) end)
+
(case when count(c.id) is null then 0 else count(c.id) end) as 总数,
(case when ((case when count(b.id) is null then 0 else count (b.id) end)
+(case when count(c.id) is null then 0 else count(c.id) end)
) =0
then 0
else (case when count(b.id) is null then 0 else count (b.id) end)
/
((case when count(b.id) is null then 0 else count (b.id) end)
+(case when count(c.id) is null then 0 else count(c.id) end)
) as 订单率
from table3 a left join table1 b on a.agent_id=b.agent_id
left join table2 c on a.agent_id=c.agent_id
group by a.agent_id,a.name
,订单率=cast(cast(isnull(订单数,0)*100.0/总数 as decimal(20,2)) as varchar)+'%'
from(
select agent_id=isnull(a.agent_id,b.agent_id),a.订单数,b.报号数,b.咨询数
,总数=isnull(a.订单数,0)+isnull(b.报号数,0)+isnull(b.咨询数,0)
from(
select agent_id,订单数=count(*) from table1 group by agent_id
) a full join(
select agent_id
,报号数=sum(case type when '报号数' then 1 else 0 end)
,咨询数=sum(case type when '咨询数' then 1 else 0 end)
from table2 group by agent_id
) b on a.agent_id=b.agent_id
) a join table3 b on a.agent_id=b.agent_id