select x.plan_id ,x.name,nvl(y.notice_type,0) notice_type,y.notice_string from ( select a.plan_id,a.name from custcare.eting_plan a , (select plan_id,sum(cnt) cnts from ( select plan_id,count(*) cnt from present_mobile_cfg group by plan_id union all select plan_id,count(*) cnt from present_ticket_cfg group by plan_id union all select plan_id,count(*) cnt from present_gift_cfg group by plan_id union all select plan_id,count(*) cnt from present_valuecard_cfg group by plan_id) group by plan_id) b where a.plan_id=b.plan_id and a.plan_id>200000000000 and a.plan_id<200999999999 ) x
left join (select r.plan_id,r.notice_type,r.notice_string from eting_notice_cfg r where r.plan_id>200000000000 and r.plan_id<209999999999 and r.notice_type=2)
y
on x.plan_id=y.plan_id这样比较清楚点
一、简化以后的[说明:内层嵌套的union all->union ,group gy 去掉。]> select x.plan_id ,x.name,nvl(y.notice_type,0) notice_type,y.notice_string from (select a.plan_id,a.name from custcare.eting_plan a , (select plan_id from present_mobile_cfg union select plan_id from present_ticket_cfg union select plan_id from present_gift_cfg union select plan_id from present_valuecard_cfg ) b where a.plan_id=b.plan_id and a.plan_id>200000000000 and a.plan_id<200999999999 ) x left join (select r.plan_id,r.notice_type,r.notice_string from eting_notice_cfg r where r.plan_id>200000000000 and r.plan_id<209999999999 and r.notice_type=2 ) y on x.plan_id=y.plan_id
二、简化后的[提议,版主以后要注意格式ya!]> select a.plan_id,a.name ,nvl(y.notice_type,0) notice_type,y.notice_string from eting_plan a, credit_release_cfg b , (select c.plan_id,c.notice_type, c.notice_string from eting_notice_cfg c where c.plan_id>200000000000 and c.plan_id<200999999999 and c.notice_type =1 ) y where a.plan_id=b.plan_id and a.plan_id>200000000000 and a.plan_id<200999999999 and a.release_type=2 and b.release_type=1 and a.plan_id=y.plan_id(+)
from ( select a.plan_id,a.name
from custcare.eting_plan
a ,
(select plan_id,sum(cnt) cnts from (
select plan_id,count(*) cnt from present_mobile_cfg group by plan_id
union all select plan_id,count(*) cnt from present_ticket_cfg group by plan_id
union all select plan_id,count(*) cnt from present_gift_cfg group by plan_id
union all select plan_id,count(*) cnt from present_valuecard_cfg group by plan_id) group by plan_id)
b
where a.plan_id=b.plan_id and a.plan_id>200000000000 and a.plan_id<200999999999 )
x
left join
(select r.plan_id,r.notice_type,r.notice_string from eting_notice_cfg r
where r.plan_id>200000000000 and r.plan_id<209999999999 and r.notice_type=2)
y
on x.plan_id=y.plan_id这样比较清楚点
select x.plan_id ,x.name,nvl(y.notice_type,0) notice_type,y.notice_string
from (select a.plan_id,a.name
from custcare.eting_plan a ,
(select plan_id from present_mobile_cfg
union select plan_id from present_ticket_cfg
union select plan_id from present_gift_cfg
union select plan_id from present_valuecard_cfg
) b
where a.plan_id=b.plan_id
and a.plan_id>200000000000
and a.plan_id<200999999999
) x
left join (select r.plan_id,r.notice_type,r.notice_string
from eting_notice_cfg r
where r.plan_id>200000000000
and r.plan_id<209999999999
and r.notice_type=2
) y
on x.plan_id=y.plan_id
select a.plan_id,a.name ,nvl(y.notice_type,0) notice_type,y.notice_string
from eting_plan a,
credit_release_cfg b ,
(select c.plan_id,c.notice_type, c.notice_string
from eting_notice_cfg c
where c.plan_id>200000000000 and c.plan_id<200999999999 and c.notice_type =1 ) y
where a.plan_id=b.plan_id
and a.plan_id>200000000000
and a.plan_id<200999999999
and a.release_type=2
and b.release_type=1
and a.plan_id=y.plan_id(+)