Oracle子查询重构如何返回过滤后的行及其所有祖先

sql oracle

34 观看

1回复

16 作者的声誉

我正在使用表格在应用程序中建模多级菜单:

CREATE TABLE
    CWE_MENUS
    (
        NODE_ID INTEGER NOT NULL,
        PARENT_NODE INTEGER,
        INLINE_SORT_NO INTEGER NOT NULL,
        ICON VARCHAR2(32),
        TEXT VARCHAR2(32) NOT NULL,
        ACTION_TYPE VARCHAR2(32),
        ACTION_INFO VARCHAR2(512),
        ENABLED CHAR(1),
        REMARK VARCHAR2(128),
        PRIMARY KEY (NODE_ID),
        CONSTRAINT CWE_MENUS_FK1 FOREIGN KEY (PARENT_NODE) REFERENCES "CWE_MENUS" ("NODE_ID")
    );

我使用这样的SQL来获取所有菜单数据:

select  T.*, case when exists(select node_id from cwe_menus D where D.parent_node=T.node_id) then 0 else 1 end as leaf from (
    WITH link(NODE_ID, PARENT_NODE, INLINE_SORT_NO, ICON, TEXT, ACTION_TYPE, ACTION_INFO, ENABLED,  REMARK, lvl,pth) AS (
        SELECT NODE_ID, PARENT_NODE, INLINE_SORT_NO, ICON, TEXT, ACTION_TYPE, ACTION_INFO, ENABLED, REMARK, 1 as lvl, ''||node_id as pth
        FROM cwe_menus
        where parent_node is null
        UNION ALL
        SELECT p2.NODE_ID, p2.PARENT_NODE, p2.INLINE_SORT_NO, p2.ICON, p2.TEXT, p2.ACTION_TYPE, p2.ACTION_INFO, p2.ENABLED,  p2.REMARK, p1.lvl + 1 as lvl, p1.pth||'.'||p2.node_id as pth
        FROM link p1 INNER JOIN cwe_menus p2 ON p1.node_id = p2.parent_node
    )
    SEARCH DEPTH FIRST BY INLINE_SORT_NO SET order1
    SELECT distinct * FROM link ORDER BY order1
) T 

这就像魔术一样起作用,但是如何在保持树结构的同时应用一些条件来过滤数据?我的意思是返回所有匹配的行及其祖先行。我还需要按INLINE_SORT_NO排序的所有同级,还需要LVL, PATH, LEAF列。

我已经查看了oracle文档和Internet上的一些教程,但是他们都没有提到如何做到这一点,对不起我的英语不好。

作者: OldFarmer 的来源 发布者: 2017 年 9 月 15 日

回应 1


0

848 作者的声誉

只需使用分层查询 -

SELECT v.*, connect_by_isleaf as LEAF, level as LVL, sys_connect_by_path(text,'.') as PATH
FROM 
    (
      SELECT s.*
        FROM cwe_menus s
     CONNECT BY PRIOR parent_node = node_id
       START WITH (text = 3) /* filter condition*/
    ) v
CONNECT BY PRIOR node_id = parent_node
START WITH parent_node IS NULL
ORDER SIBLINGS BY inline_sort_no
作者: Stawros 发布者: 2017 年 9 月 15 日
32x32