oracle 编写查询以返回基于级别(p2)的父位置

r7xajy2e 于 2023-06-05 发布在 Oracle

关注(0)|答案(1)|浏览(484)

在myx 1 e0f1x中，我可以解决如何获取位置层次结构。但似乎我有另一个问题时，试图与系统集成
所以我有两张table
位置（约20 k条记录）
| id|姓名|活跃|1级|2级|三级|4级|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 1|一个|真的|一个|零|零|零|
| 2| A>B|真的|一个|B|零|零|
| 3| A>B>C|真的|一个|B| C类|零|
| 4| A>B>C>D|真的|一个|B| C类|D|
| 5个|X型|真的|X型|零|零|零|
日志（约500万条记录）
| id|姓名|位置标识|
| - -----|- -----|- -----|
| 1|日志001| 3|
| 2|日志002| 5个|
因此，目标是我想获取只有日志的位置层次结构。问题是当我尝试与日志表连接时，它有一个性能问题。查询似乎需要10+分钟
下面是我的查询的外观demo我正在尝试此查询

SELECT *
FROM   location
START WITH id IN (SELECT DISTINCT location_id FROM log_response)
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORDER BY NAME

或

WITH cte(location_id) AS (
     SELECT DISTINCT location_id FROM log_response
)
SELECT *
FROM   location
START WITH id IN (SELECT location_id FROM cte)
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORDER BY NAME

p/s上例中的查询的期望值应该返回location have id in（1，2，3，5）
| id|姓名|活跃|1级|2级|三级|4级|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 1|一个|真的|一个|零|零|零|
| 2| A>B|真的|一个|B|零|零|
| 3| A>B>C|真的|一个|B| C类|零|
| 5个|X型|真的|X型|零|零|零|
非常感谢！

oracle

来源：https://stackoverflow.com/questions/76359166/write-a-query-to-return-parent-location-base-on-level-p2

1条答案

按热度按时间

zzzyeukh1#

您可以使用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;

输出：
| ID|名称|激活|1级|2级|3级|4级|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 3| A>B>C|真的|一个|B| C类|联系我们|
| 2| A>B|真的|一个|B|联系我们|联系我们|
| 1|一个|真的|一个|联系我们|联系我们|联系我们|
| 5个|X型|真的|X型|联系我们|联系我们|联系我们|
或者：

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

赞(0）回复(0）举报 2023-06-05

我来回答

oracle 编写查询以返回基于级别(p2)的父位置

1条答案

相关问题

热门标签

最新问答