Sql 编写查询以根据级别 (p2) 返回父位置

您可以使用EXISTS(而不是IN和DISTINCT):

SELECT *
FROM   location l
START WITH EXISTS(SELECT 1 FROM log WHERE location_id = l.id)
CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)

其中，对于样本数据:

CREATE TABLE location (id, Name, is_active, level1, level2, level3, level4) AS
SELECT 1, 'A',       'true', 'A', null, null, null FROM DUAL UNION ALL
SELECT 2, 'A>B',     'true', 'A', 'B',  null, null FROM DUAL UNION ALL
SELECT 3, 'A>B>C',   'true', 'A', 'B',  'C',  null FROM DUAL UNION ALL
SELECT 4, 'A>B>C>D', 'true', 'A', 'B',  'C',  'D'  FROM DUAL UNION ALL
SELECT 5, 'X',       'true', 'X', null, null, null FROM DUAL;

CREATE TABLE log (id, Name, location_id) AS
SELECT 1, 'log 001', 3 FROM DUAL UNION ALL
SELECT 2, 'log 002', 5 FROM DUAL;

输出:

或者:

SELECT DISTINCT *
FROM   location l
START WITH EXISTS(SELECT 1 FROM log WHERE location_id = l.id)
CONNECT BY 
       PRIOR level1 = level1
AND    (  (   level2 IS NULL AND level3 IS NULL AND level4 IS NULL
          AND PRIOR level2 IS NOT NULL AND PRIOR level3 IS NULL AND PRIOR level4 IS NULL
          )
          OR (   PRIOR level2 = level2
             AND ( (   level3 IS NULL AND level4 IS NULL
                   AND PRIOR level3 IS NOT NULL AND PRIOR level4 IS NULL
                   )
                 OR (   PRIOR level3 = level3
                    AND level4 IS NULL AND PRIOR level4 IS NOT NULL
                    )
                 )
              )
      )

fiddle个