如何在层次结构(通过)连接的oracle查询中省略较少的父节点? [英] how do I omit parent less nodes in an hierarchical (connect by) oracle query?

问题描述

给带有列的名为CATEGORY的表

Given a table named CATEGORY with the columns

• CATEGORY_NAME
• CATEGORY_PARENT_NAME
• 有效

这个查询

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子句中进行了过滤也是如此.我做错了什么?

I'm trying to get only the active child nodes whose the root parents are active, but oracle also returns the active child nodes of the inactive roots, even through their parents were filtered in the where clause. I'm doing something wrong?

推荐答案

您应该修改START BY子句以包含ACTIVE = TRUE，以便仅考虑那些活动的根.

You should modify your START BY clause to include ACTIVE = TRUE, so that only those roots which are active will be considered.

WHERE子句将在以后应用，以便您只能过滤掉ACTIVE子节点.

WHERE clause will be applied later, so that you can filter out only the ACTIVE child nodes.

 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;

这篇关于如何在层次结构(通过)连接的oracle查询中省略较少的父节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！