优化一个SQL语句200分

[chenchangfu]

优化这个SQL语句
select a.post_code, a.inst_code, a.post_name, to_char(b.change_date, 'YYYY-MM-DD') as change_date
from t_hr_b_post b, t_mgt_post a
where confirmflag = '00590002' and a.post_code = b.post_code and
exists(select 1
from (select max(emp_post_id) as emp_post_id
from t_hr_b_post a
where confirmflag = '00590002' and
exists(select 1
from (select emp_id, min(nvl(post_on_type, '0068002')) post_on_type
from t_hr_b_post
where confirmflag = '00590002'
group by emp_id
)b
where a.emp_id = b.emp_id and nvl(a.post_on_type, '0068002') = b.post_on_type
)
group by emp_id
) c
where c.emp_post_id = b.emp_post_id
)

[Luke121302]

顶

[chenchangfu]

谢谢了

[子陌红尘]

楼主把业务逻辑和表结构罗列出来，SQL语句看得我头大。

[chensheng913]

是呀，楼主你把你的表结构和业务逻辑给我们说一下，我们才好帮你看啊！

[pt870219]

select a.post_code, a.inst_code, a.post_name, to_char(b.change_date, 'YYYY-MM-DD') as change_date from
t_hr_b_post b, t_mgt_post a
where confirmflag = '00590002' and a.post_code = b.post_code and exists(
select 1 from(
select emp_id, min(nvl(post_on_type, '0068002')) post_on_type from t_hr_b_post where confirmflag = '00590002' group by emp_id
)b where a.emp_id = b.emp_id and nvl(a.post_on_type, '0068002') = b.post_on_type
)

[Paladin_china]

好多嵌套.一般如果不是很有时间的话大家都没有耐心看的

[chenchangfu]

exists相套执行效率就下降，把EXISTS边成连接就可以
select a.post_code, a.inst_code, a.post_name, to_char(b.change_date, 'YYYY-MM-DD') as change_date
from t_hr_b_post b, t_mgt_post a,
(select max(a.emp_post_id) as emp_post_id
from t_hr_b_post a,
(select emp_id, min(nvl(post_on_type, '0068002')) post_on_type
from t_hr_b_post
where confirmflag = '00590002'
group by emp_id
)b
where a.emp_id = b.emp_id and nvl(a.post_on_type, '0068002') = b.post_on_type
group by a.emp_id
) c
where confirmflag = '00590002' and a.post_code = b.post_code and c.emp_post_id = b.emp_post_id
谢谢大家，但都没有解决实质性问题，所以说是给两百分，现在就给一百分

[chenchangfu]

非常感谢大家

[camio007]

好多嵌套，看不过来，帮你顶了~！

[左大神在这]

帮你顶,没有时间分析,项目忙的很.