在没有连接的情况下重新编写查询。

Question

我正在重写要替换的查询以删除 的连接：

SELECT *
FROM   ADM_TRT AT
       INNER JOIN UTILISATEUR U
       ON U.UTI_ID = AT.UTI_ID
       INNER JOIN
       (
         SELECT CM.MAI_ID
         FROM   CON_MAI CM
         CONNECT BY CM.MAI_PER_RES = PRIOR CM.MAI_ID
         START WITH CM.MAI_ID IN (
                                   SELECT MAJ_ID
                                   FROM   DROIT_LOGIN
                                   WHERE LOG_ID = 21543
                                 )
       ) CON_MAI_FILTERED_ON_LOGIN
       ON AT.TRT_MAI_ID = CON_MAI_FILTERED_ON_LOGIN.MAI_ID;

对于通过 part连接，我写了

WITH tree (MAI_ID,MAI_PER_RES, level1) AS  (
  SELECT MAI_PER_RES, MAI_ID, 1 as level1 FROM CON_MAI
  UNION ALL
  SELECT child.MAI_ID, child.MAI_PER_RES, parent.level1 + 1
  FROM CON_MAI child --Line 20
       JOIN tree parent
       on parent.MAI_PER_RES = child.MAI_ID
) 
SELECT MAI_ID FROM tree

这篇子查询。有人可以帮忙整合吗？

Answer 1

看来您的递归逆转了递归的子查询，并且可以使用：

WITH tree (MAI_ID) AS  (
  SELECT MAI_ID
  FROM   CON_MAI
  WHERE  MAI_ID IN ( SELECT MAJ_ID
                     FROM   DROIT_LOGIN
                     WHERE LOG_ID = 21543 )
UNION ALL
  SELECT c.MAI_ID
  FROM   CON_MAI c
         JOIN tree p
         on c.MAI_PER_RES = p.MAI_ID
)
SELECT *
FROM   ADM_TRT AT
       INNER JOIN UTILISATEUR U
       ON U.UTI_ID = AT.UTI_ID
       INNER JOIN tree CON_MAI_FILTERED_ON_LOGIN
       ON AT.TRT_MAI_ID = CON_MAI_FILTERED_ON_LOGIN.MAI_ID;

（未经测试，因为我没有您的表格或数据）