如何在分层(连接方式)oracle查询中省略无父节点

Question

给定一个名为CATEGORY、列为的表

• 类别名称
• 类别租户名称
• 活动的

这个查询

SELECT *
FROM CATEGORY
WHERE ACTIVE = 'TRUE'
START WITH CATEGORY_PARENT_NAME IS NULL   
CONNECT BY CATEGORY_PARENT_NAME = PRIOR CATEGORY_NAME   
ORDER SIBLINGS BY MENU_INDEX

我试图只获取根父节点处于活动状态的活动子节点，但oracle也会返回非活动根的活动子结点，即使它们的父节点在where子句中被过滤。我做错了什么？

Answer 1

您应该修改START BY子句以包含ACTIVE = TRUE，这样只会考虑那些活动的根。

WHERE子句稍后将被应用，这样您就可以只筛选出ACTIVE子节点。

 select *
   from category
  where active = 'TRUE'
  start with category_parent_name is null
    and active = 'TRUE' 
connect by category_parent_name = prior category_name   
  order siblings by menu_index;

Answer 2

以下查询（请参阅SQL Fiddle）将为您提供根处于活动状态的所有活动节点：

SELECT *
FROM (
  SELECT c.*,
    PRIOR c.ACTIVE AS PARENT_ACTIVE,
    CONNECT_BY_ROOT(c.CATEGORY_NAME) AS CATEGORY_ROOT_NAME,
    CONNECT_BY_ROOT(c.ACTIVE) AS ROOT_ACTIVE
  FROM CATEGORY c
  WHERE c.ACTIVE = 'TRUE'
  START WITH c.CATEGORY_PARENT_NAME IS NULL   
  CONNECT BY c.CATEGORY_PARENT_NAME = PRIOR c.CATEGORY_NAME   
  ORDER SIBLINGS BY c.MENU_INDEX
) a
WHERE a.ROOT_ACTIVE = 'TRUE'

要进一步过滤结果，使其仅包括父节点、祖父母节点和层次结构根节点之前的所有祖先节点都处于活动状态的活动节点（请参阅SQL Fiddle）：

SELECT *
FROM (
  SELECT c.*,
    PRIOR c.ACTIVE AS PARENT_ACTIVE,
    CONNECT_BY_ROOT(c.CATEGORY_NAME) AS CATEGORY_ROOT_NAME,
    CONNECT_BY_ROOT(c.ACTIVE) AS ROOT_ACTIVE,
    SYS_CONNECT_BY_PATH(c.ACTIVE, '/') AS ACTIVE_PATH
  FROM CATEGORY c
  WHERE c.ACTIVE = 'TRUE'
  START WITH c.CATEGORY_PARENT_NAME IS NULL   
  CONNECT BY c.CATEGORY_PARENT_NAME = PRIOR c.CATEGORY_NAME   
  ORDER SIBLINGS BY c.MENU_INDEX
) a
WHERE a.ROOT_ACTIVE = 'TRUE'
  AND a.ACTIVE_PATH NOT LIKE '%FALSE%'

提示

• 使用CONNECT_BY_ROOT运算符从根节点获取列值
• 使用PRIOR运算符从父节点获取列值（当您在根节点上时，只需注意NULL s）
• 使用SYS_CONNECT_BY_PATH函数可以获取一直到根节点的列的值