SQL多个共享WHERE条件

Question

我正在尝试寻找一种好的、高效的方法来运行像这样的查询：

SELECT *
  FROM tableA a
 WHERE    a.manager IN (    SELECT id
                                 FROM tableB b
                           CONNECT BY PRIOR b.id = b.manager_id
                           START WITH b.id = 'managerBob')
       OR a.teamLead IN (    SELECT ID
                               FROM tableB b
                         CONNECT BY PRIOR b.ID = b.manager_id
                         START WITH b.ID = 'managerBob')
       OR a.creator IN (    SELECT id
                              FROM tableB b
                        CONNECT BY PRIOR b.id = b.manager_id
                        START WITH b.id = 'managerBob')

您可以看到，我正在尝试使用多个WHERE子句，但每个子句都在等式的右侧使用相同的数据集。如果我使用多个子句，它似乎运行非常缓慢，我很确定这是因为Oracle正在运行每个子查询。有没有办法使这样的东西工作？

SELECT *
  FROM tableA a
 WHERE    a.manager, 
          a.teamLead, 
          a.creator in (    SELECT id
                                 FROM tableB b
                           CONNECT BY PRIOR b.id = b.manager_id
                           START WITH b.id = 'managerBob')

顺便说一下，如果我可以通过谷歌搜索得到这个信息，我很抱歉，我不确定该如何称呼这个东西。

- monitorjbl

Answer 1

子查询优化 可能会有所帮助:

WITH people AS
(    SELECT id
       FROM tableB b
    CONNECT BY PRIOR b.id = b.manager_id
      START WITH b.id = 'managerBob'
)
SELECT *
  FROM tableA a
 WHERE    a.manager IN (SELECT id FROM people)
       OR a.teamLead IN (SELECT id FROM people)
       OR a.creator IN (SELECT id FROM people)

- Tony Andrews

---

这个语法让我很不爽，但就执行速度而言，它是最快的。谢谢你告诉我它叫什么，那是我试图找出它的最令人沮丧的部分 :) - monitorjbl

Answer 2

您可以做以下事情：

WITH bob_subordinates AS (
(    SELECT id
       FROM tableB b
 CONNECT BY PRIOR b.id = b.manager_id
 START WITH b.id = 'managerBob')
SELECT * FROM tableA a
 WHERE a.manager in  (select id from bob_subordinates)
    OR a.teamlead in (select id from bob_subordinates)
    or a.creator  in (select id from bob_subordinates)

替代方案（请检查DISTINCT的使用：如果B表中的id不唯一，则这不是等效的）：

WITH bob_subordinates AS (
(    SELECT DISTINCT id
       FROM tableB b
 CONNECT BY PRIOR b.id = b.manager_id
 START WITH b.id = 'managerBob')
SELECT DISTINCT a.*
  FROM tableA a JOIN bob_subordinates b ON b.id IN (a.manager, a.teamlead, a.creator);

- Benoit

Answer 3

根据评论更新 - 请尝试

SELECT A.* FROM 
(SELECT bb.id FROM tableB bb CONNECT BY PRIOR bb.id = bb.manager_id START WITH bb.id = 'managerBob') B INNER JOIN TABLEA A ON B.ID IN (A.MANAGER, A.TEAMLEAD, A.CREATOR)

- Yahia

---

你可以简化一下 WHERE 子句：WHERE b.id in (a.manager, a.teamlead, a.creator)。 - Josh

我希望人们停止试图教别人使用,而不是JOIN :( - MatBailie

这个方法是可行的，而且我更喜欢这种语法，但是Oracle处理它的速度不如WITH()解决方案快。使用这种语法比使用WITH要多花费50-70%的执行时间。我不确定原因是什么，可能只是Oracle以某种方式优化了WITH语句。 - monitorjbl