-- test
create table #t1(id int,order_id int,remove varchar(1))
insert #t1
select 1,1,'y'
union select 2,1,'n'
union select 3,2,'n'
union select 4,3,'n'create table #t2(id int,agent_id int,state varchar(4))
insert #t2
select 1,100,'成功'
union select 2,101,'失败'
union select 3,100,'成功'select * from #t1select * from #t2select a.agent_id as 话务员工号,a.aCount as 订单数,b.成功票数 from
(select agent_id,count(*) as aCount from #t2 group by agent_id) a,
(
select agent_id,成功票数=sum(case when state='成功' and remove='n' then 1 else 0 end)
from #t1 a,#t2 b where b.id=a.order_id group by agent_id
) b
where a.agent_id = b.agent_iddrop table #t1,#t2
create table #t1(id int,order_id int,remove varchar(1))
insert #t1
select 1,1,'y'
union select 2,1,'n'
union select 3,2,'n'
union select 4,3,'n'create table #t2(id int,agent_id int,state varchar(4))
insert #t2
select 1,100,'成功'
union select 2,101,'失败'
union select 3,100,'成功'select * from #t1select * from #t2select a.agent_id as 话务员工号,a.aCount as 订单数,b.成功票数 from
(select agent_id,count(*) as aCount from #t2 group by agent_id) a,
(
select agent_id,成功票数=sum(case when state='成功' and remove='n' then 1 else 0 end)
from #t1 a,#t2 b where b.id=a.order_id group by agent_id
) b
where a.agent_id = b.agent_iddrop table #t1,#t2
id int,
agent_id int,
state nvarchar(20)
)
declare @table1 table(
id int,
order_id int,
remove nvarchar(20)
)insert into @table2
select 1,100,'成功'
union all
select 2,101,'失败'
union all
select 3,100,'成功'insert into @table1
select 1,1,'y'
union all
select 2,1,'n'
union all
select 3,2,'n'
union all
select 4,4,'n' select a.agent_id as 话务员工号,sum(a.acount) as 订单数,sum(b.成功票数) 成功票数
from
(select agent_id,id,count(*) as aCount from @table2 group by agent_id,id) a,
(select table2.agent_id,table1.order_id,成功票数=sum(case when (table2.state='成功'
and remove='n') then 1 else 0 end )from @table1 table1,@table2 table2 where table2.id=table1.order_id group by table2.agent_id,table1.order_id) b
where a.agent_id=b.agent_id
group by a.agent_id结果:
100 2 2
101 1 0
好像错了。。
执行您的语句报错:列名agent_id不明确
执行您的语句报错:列名agent_id不明确
---我写的测试是可以的,不会报错,你将我的代码粘贴到查询分析器里执行看看。
然后根据你的情况修改一下表名等。