Optimizing an Oracle SQL Query

Here is a query I have that returns the chain of supervisors for an employee but it uses a bunch of nested SELECT statements. I'd like to know whether this query could be refactored to be more efficient. The query is for an application where 3 levels of management authorize an employee to take a training class. Currently we require 3 levels of approvals, but this could change to 4 or more.

SELECT badge as employee, 
   supervisor_badge as boss1, 
   (select supervisor_badge FROM hr_data level2 WHERE badge = level1.supervisor_badge) as boss2
   (select supervisor_badge FROM hr_data level3 WHERE badge = 
           (select supervisor_badge FROM hr_data level2 WHERE badge = level1.supervisor_badge)) as boss3
   FROM hr_data level1 WHERE BADGE = '123456';

badge = the employee's ID
supervisor_badge = the badge of the employee's supervisor
bothe fields are in the hr_data table

           badge    supervisor_badge 
           123456   111111
           111111   454545
           454545   332211

output

 employee       boss1      boss2      boss3
 123456         111111     454545     332211

链接地址: http://www.djcxy.com/p/93930.html

上一篇: 在Oracle CONNECT BY分层查询中引用父列

下一篇: 优化Oracle SQL查询