优化 Oracle SQL 查询

发布于 2024-11-02 11:05:27 字数 874 浏览 3 评论 0原文

这是我的一个查询，它返回员工的主管链，但它使用了一堆嵌套的 SELECT 语句。我想知道这个查询是否可以重构以提高效率。该查询针对一个应用程序，其中 3 个级别的管理层授权员工参加培训课程。目前我们需要 3 级批准，但这可能会更改为 4 级或更多。

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';

徽章 = 员工 ID
Supervisor_badge = 员工主管的徽章
Bothe 字段位于 hr_data 表

           badge    supervisor_badge 
           123456   111111
           111111   454545
           454545   332211

输出中

 employee       boss1      boss2      boss3
 123456         111111     454545     332211

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

当梦初醒 2024-11-09 11:05:27

我没有方便的数据库来模拟这个，所以我会即兴发挥。为了清楚起见，我尝试使用相同的命名约定。

SELECT   level1.badge as employee
        ,level2.badge as boss1
        ,level3.badge as boss2
        ,level3.supervisor_badge as boss3
FROM    hr_data level1 
        INNER JOIN hr_data level2 
            ON level2.badge = level1.supervisor_badge
        INNER JOIN hr_data level3 
            ON level3.badge = level2.supervisor_badge
WHERE       level1.badge = '123456'

重要提示：这只会返回所有连接处都存在数据的记录。要返回少于 3 个老板的记录（即只有老板 1 和老板 2，但没有老板 3），请将 INNER JOIN 语句更改为 LEFT JOIN。

I don't have a database handy, to mock this up, so I'll wing it. I tried to use your same naming conventions, for clarity.

SELECT   level1.badge as employee
        ,level2.badge as boss1
        ,level3.badge as boss2
        ,level3.supervisor_badge as boss3
FROM    hr_data level1 
        INNER JOIN hr_data level2 
            ON level2.badge = level1.supervisor_badge
        INNER JOIN hr_data level3 
            ON level3.badge = level2.supervisor_badge
WHERE       level1.badge = '123456'

IMPORTANT NOTE: This will only return records where data exists at all joins. To return records that have fewer than 3 bosses (i.e. only boss1 and boss2, but no boss3), change the INNER JOIN statements to LEFT JOIN.

回复收藏 0 原文

鹤舞 2024-11-09 11:05:27

使用连接而不是子查询。

SELECT
    e.badge, b1.badge, b2.badge, b3.badge
FROM
    hr_data e
LEFT JOIN hr_data b1 ON e.badge=b1.badge
LEFT JOIN hr_data b2 ON b1.badge=b2.badge
LEFT JOIN hr_data b3 ON b2.badge=b3.badge
WHERE
    e.badge='123456';

由于级别是可变的，您可能需要考虑使用存储过程在内部循环预先指定的级别数（直到到达顶部，例如您的 CEO）。

Use joins rather than subqueries.

SELECT
    e.badge, b1.badge, b2.badge, b3.badge
FROM
    hr_data e
LEFT JOIN hr_data b1 ON e.badge=b1.badge
LEFT JOIN hr_data b2 ON b1.badge=b2.badge
LEFT JOIN hr_data b3 ON b2.badge=b3.badge
WHERE
    e.badge='123456';

As the level is variable, you may want to consider using a Stored Procedure to internally loop for the prespecified number of levels (until it hits the top, e.g., your CEO).

回复收藏 0 原文

~没有更多了~