select id,name as 处理人,(select c.name
from A a,B b,C c
where a.id = b.Aid and c.id = b.Cid and a.id=t.id) 所属机构 from(
select a.id,a.name,
sum(case when d.STATUS=0 then 1 else 0 end) as 待处理,
sum(case when d.STATUS=1 then 1 else 0 end) as 带跟进,
sum(case when d.STATUS=2 then 1 else 0 end) as 已结束,
sum(case when d.STATUS=3 then 1 else 0 end) as 已完成
from D d,A a
where a.id = d.Aid (d表的Aid和a表的id对应,但d表有多条数据的Aid=a.id)
group by a.id
) t
select id,name, name_c 所属机构, BB.待处理, BB.带跟进, BB.已结束, BB.已完成 from
(select a.id, a.name, c.name name_c
from A a,B b,C c
where a.id = b.Aid and c.id = b.Cid and a.id=t.id) AA,
(select a.id,a.name,
sum(case when d.STATUS=0 then 1 else 0 end) as 待处理,
sum(case when d.STATUS=1 then 1 else 0 end) as 带跟进,
sum(case when d.STATUS=2 then 1 else 0 end) as 已结束,
sum(case when d.STATUS=3 then 1 else 0 end) as 已完成
from D d,A a
where a.id = d.Aid
group by a.id) BB
where AA.id = bb.id