select r.region_name, count1,sum1,dcount1,dsum1,ycount1,ysum1
,count2,sum2,dcount2,dsum2,ycount2,ysum2 from
spms_misc_region r ,(select count(*) count1,sum(amount) sum1 ,region2 from spms_mbo_intch_adv_view t
where t.project_state='2'
group by region2) t1,(select count(*) dcount1,sum(amount) dsum1,region2 from spms_mbo_intch_adv_view t
where t.project_state='1'
group by region2)t2,
(select count(*) ycount1,sum(amount)ysum1, region2 from spms_mbo_intch_adv_view t
where t.haveorder = '1'
group by region2) t3,
(select count(*) count2,sum(amount) sum2,region2 from spms_mbo_rsl_adv_view t
where t.project_state='2'
group by region2) f1,
(select count(*) dcount2,sum(amount) dsum2,region2 from spms_mbo_rsl_adv_view t
where t.project_state='1'
group by region2)f2,
(select count(*) ycount2,sum(amount)ysum2,region2 from spms_mbo_rsl_adv_view t
where t.haveorder = '1'
group by region2) f3
where r.region_id = t1.region2(+)
and r.region_id = t2.region2(+)
and r.region_id = t3.region2(+)
and r.region_id = f1.region2(+)
and r.region_id = f2.region2(+)
and r.region_id = f3.region2(+)
and r.LVL ='2'
--cp.project_state
,count2,sum2,dcount2,dsum2,ycount2,ysum2 from
spms_misc_region r ,(select count(*) count1,sum(amount) sum1 ,region2 from spms_mbo_intch_adv_view t
where t.project_state='2'
group by region2) t1,(select count(*) dcount1,sum(amount) dsum1,region2 from spms_mbo_intch_adv_view t
where t.project_state='1'
group by region2)t2,
(select count(*) ycount1,sum(amount)ysum1, region2 from spms_mbo_intch_adv_view t
where t.haveorder = '1'
group by region2) t3,
(select count(*) count2,sum(amount) sum2,region2 from spms_mbo_rsl_adv_view t
where t.project_state='2'
group by region2) f1,
(select count(*) dcount2,sum(amount) dsum2,region2 from spms_mbo_rsl_adv_view t
where t.project_state='1'
group by region2)f2,
(select count(*) ycount2,sum(amount)ysum2,region2 from spms_mbo_rsl_adv_view t
where t.haveorder = '1'
group by region2) f3
where r.region_id = t1.region2(+)
and r.region_id = t2.region2(+)
and r.region_id = t3.region2(+)
and r.region_id = f1.region2(+)
and r.region_id = f2.region2(+)
and r.region_id = f3.region2(+)
and r.LVL ='2'
--cp.project_state
select r.region_name, count1,sum1,dcount1,dsum1,ycount1,ysum1
,count2,sum2,dcount2,dsum2,ycount2,ysum2 from
(SELECT region_name FROM spms_misc_region WHERE LVL ='2') r
,
(select count(*) count1,sum(amount) sum1 ,region2 from spms_mbo_intch_adv_view t
where t.project_state='2'
group by region2) t1,(select count(*) dcount1,sum(amount) dsum1,region2 from spms_mbo_intch_adv_view t
where t.project_state='1'
group by region2)t2,
(select count(*) ycount1,sum(amount)ysum1, region2 from spms_mbo_intch_adv_view t
where t.haveorder = '1'
group by region2) t3,
(select count(*) count2,sum(amount) sum2,region2 from spms_mbo_rsl_adv_view t
where t.project_state='2'
group by region2) f1,
(select count(*) dcount2,sum(amount) dsum2,region2 from spms_mbo_rsl_adv_view t
where t.project_state='1'
group by region2)f2,
(select count(*) ycount2,sum(amount)ysum2,region2 from spms_mbo_rsl_adv_view t
where t.haveorder = '1'
group by region2) f3
where r.region_id = t1.region2(+)
and r.region_id = t2.region2(+)
and r.region_id = t3.region2(+)
and r.region_id = f1.region2(+)
and r.region_id = f2.region2(+)
and r.region_id = f3.region2(+)
from spms_misc_region r,spms_mbo_rsl_adv_view s
where r.region_id = s.region2(+)
and r.LVL ='2'
select r.region_name
,count(Case When t.project_state='2' Then 1 End ) count1
,Sum(Case When t.project_state='2' Then amount End ) sum1
,count(Case When t.project_state='1' Then 1 End ) dcount1
,Sum(Case When t.project_state='1' Then amount End ) dsum1
,count(Case When t.haveorder = '1' Then 1 End ) ycount1
,Sum(Case When t.haveorder = '1' Then amount End ) ysum1
,count(Case When f.project_state='2' Then 1 End ) count2
,Sum(Case When f.project_state='2' Then amount End ) sum2
,count(Case When f.project_state='1' Then 1 End ) dcount2
,Sum(Case When f.project_state='1' Then amount End ) dsum2
,count(Case When f.haveorder = '1' Then 1 End ) ycount2
,Sum(Case When f.haveorder = '1' Then amount End ) ysum2
from spms_misc_region r
Left Join spms_mbo_rsl_adv_view t On r.r.region_id = t.region2
Left Join spms_mbo_rsl_adv_view f On r.r.region_id = f.region2
Where r.LVL ='2'
,count(Case When t.project_state='2' Then 1 End ) count1
,Sum(Case When t.project_state='2' Then amount End ) sum1
,count(Case When t.project_state='1' Then 1 End ) dcount1
,Sum(Case When t.project_state='1' Then amount End ) dsum1
,count(Case When t.haveorder = '1' Then 1 End ) ycount1
,Sum(Case When t.haveorder = '1' Then amount End ) ysum1
,count(Case When f.project_state='2' Then 1 End ) count2
,Sum(Case When f.project_state='2' Then amount End ) sum2
,count(Case When f.project_state='1' Then 1 End ) dcount2
,Sum(Case When f.project_state='1' Then amount End )dsum2
,count(Case When f.haveorder = '1' Then 1 End ) ycount2
,Sum(Case When f.haveorder = '1' Then amount End ) ysum2
from spms_misc_region r
Left Join spms_mbo_intch_adv_view t On r.r.region_id = t.region2
Left Join spms_mbo_rsl_adv_view f On r.r.region_id = f.region2
Where r.LVL ='2'
select r.region_name
,count(Case When t.project_state='2' Then 1 End ) count1
,Sum(Case When t.project_state='2' Then amount End ) sum1
,count(Case When t.project_state='1' Then 1 End ) dcount1
,Sum(Case When t.project_state='1' Then amount End ) dsum1
,count(Case When t.haveorder = '1' Then 1 End ) ycount1
,Sum(Case When t.haveorder = '1' Then amount End ) ysum1
,count(Case When f.project_state='2' Then 1 End ) count2
,Sum(Case When f.project_state='2' Then amount End ) sum2
,count(Case When f.project_state='1' Then 1 End ) dcount2
,Sum(Case When f.project_state='1' Then amount End )dsum2
,count(Case When f.haveorder = '1' Then 1 End ) ycount2
,Sum(Case When f.haveorder = '1' Then amount End ) ysum2
from spms_misc_region r
Left Join spms_mbo_intch_adv_view t On r.region_id = t.region2
Left Join spms_mbo_rsl_adv_view f On r.region_id = f.region2
Where r.LVL ='2'
select a.region_id,a.region_name,
count(decode(b.project_state,'2',1)) count1,sum(decode(b.project_state,'2',b.amount)) sum1,
count(decode(b.project_state,'1',1)) dcount1,sum(decode(b.project_state,'1',b.amount)) dsum1,
count(decode(b.haveorder,'1',1)) ycount1,sum(decode(b.haveorder,'1',b.amount)) ysum1,
count(decode(c.project_state,'2',1)) count2,sum(decode(c.project_state,'2',c.amount)) sum2,
count(decode(c.project_state,'1',1)) dcount2,sum(decode(c.project_state,'1',c.amount)) dsum2,
count(decode(c.haveorder,'2',1)) ycount2,sum(decode(c.haveorder,'2',c.amount)) ysum2
from spms_misc_region a,spms_mbo_intch_adv_view b,spms_mbo_rsl_adv_view c
where a.region_id = b.region2(+)
and a.region_id = c.region2(+)
group by a.region_id,a.region_name
group by r.region_name
了。看来真得休息两天了。