try:
select name,sum(sl) 工单总数,sum(decode(type,'咨询',sl,0)) 咨询数量,
sum(decode(type,'取消',sl,0)) 取消数量,sum(decode(type,'投诉',sl,0)) 投诉数量
from (
select t1.name,t3.type,count(*) sl from table1 t1,table2 t2,table3 t3
where t1.comid=t2.comid and t2.tid=t3.tid
group by t1.name,t3.type
) t group by name;
select name,sum(sl) 工单总数,sum(decode(type,'咨询',sl,0)) 咨询数量,
sum(decode(type,'取消',sl,0)) 取消数量,sum(decode(type,'投诉',sl,0)) 投诉数量
from (
select t1.name,t3.type,count(*) sl from table1 t1,table2 t2,table3 t3
where t1.comid=t2.comid and t2.tid=t3.tid
group by t1.name,t3.type
) t group by name;
sum(decode(type,'取消',sl,0)) 取消数量,sum(decode(type,'投诉',sl,0)) 投诉数量
from (
select t1.name,t3.type,count(*) sl from table1 t1,table2 t2,table3 t3
where t1.comid=t2.comid and t2.tid=t3.tid
group by t1.name,t3.type
) t group by name
union all
select name,0,0,0,0 from table1
where not exists(select 1 from table2 where table2.comid=table1.comid);
t1.comid=t2.comid(+) and t2.tid=t3.tid(+)
应该就可以了!
就是还有个“新飞华”还没有出来NAME 工单总数 咨询数量 取消数量 投诉数量
-------------------- ---------- ---------- ---------- ----------
电信公司 2 0 1 1
惠普公司 3 3 0 0
中兴公司 3 1 1 1
比亚迪公司 0 0 0 0
count(c.wsid) 工单总数,
count(decode(c.type,'咨询',c.wsid ,null)) 咨询数量,
count(decode(c.type,'取消',c.wsid ,null)) 取消数量,
count(decode(c.type,'投诉',c.wsid ,null)) 投诉数量
from table1 a, table2 b, table3 c
where a.comid=b.comid(+)
and b.tid=c.tid(+)
group by a.comid,a.name
order by a.comid
sum(decode(type,'取消',sl,0)) 取消数量,sum(decode(type,'投诉',sl,0)) 投诉数量
from (
select t1.name,t3.type,count(*) sl from table1 t1,table2 t2,table3 t3
where t1.comid=t2.comid and t2.tid=t3.tid
group by t1.name,t3.type
) t group by name
union all
select name,0,0,0,0 from table1
where not exists(select 1 from table2,table3 where table2.comid=table1.comid and table2.tid=table3.tid)
table2中有 新飞华公司 的数据
3 5table3中却没有
tid=3的数据。