时间戳

首页 > 解决方案 > 创建 SQL 视图以查询节点是否是特定节点的后代

sql - 创建 SQL 视图以查询节点是否是特定节点的后代

问题描述

我有 2 个具有以下数据结构的 SQL 表：

表 1：收藏夹

列：

表 2：树

列：

我想从中创建一个视图，以便我可以查询一个节点是否是收藏节点的收藏/后代。因此，对于 FAVORITES 中的每个条目，视图将有几个条目，其中用户与收藏的节点或其后代相关联。

查看：FAV_OR_DESCENDANT

列：

查询会像这样工作

SELECT *
FROM FAV_OR_DESCENDANT
WHERE fk_user = 0

对于给定的树，我期望得到的结果如下所示：

树：

TREE:
+--------+----+----------------+
| rownum | pk | pk_parent_node |
+--------+----+----------------+
|      1 |  1 | null           |
|      2 |  2 | 1              |
|      3 |  3 | 2              |
|      4 |  4 | 1              |
+--------+----+----------------+

FAVORITES:
+--------+----+---------+--------------+
| rownum | pk | fk_user | fk_tree_node |
+--------+----+---------+--------------+
|      1 |  0 |       0 |            1 |
+--------+----+---------+--------------+

Tree representation:

       1 <-- User 0 has only favorited this single node
      / \
     2   4
    /
   3

FAV_OR_DESCENDANT 中的结果数据：

+--------+---------+--------------+
| rownum | fk_user | fk_tree_node |
+--------+---------+--------------+
|      1 |       0 |            1 |
|      2 |       0 |            2 |
|      3 |       0 |            3 |
|      4 |       0 |            4 |
+--------+---------+--------------+

如果我要询问特定用户的所有收藏节点/后代节点，我知道如何编写此查询。但是，我正在努力将其转换为可以创建视图的 SQL 查询：

SELECT DISTINCT *
FROM tree
START WITH tree.pk IN (
    SELECT fk_tree_node
    FROM favorites
    WHERE fk_user = 0
)
CONNECT BY PRIOR tree.pk = tree.fk_parent_node

我发现的其他问题更集中于查询或不受 SQL 限制。我会感谢正确方向的每一个提示。

标签： sqloracleviewtreerelational-database

解决方案

  1. 由于您需要获取所有后代节点，因此您需要聚合所有父节点，例如使用sys_connect_by_path（或者您可以使用分层递归子查询分解）：
with 
TREE(pk, pk_parent_node) as (
    select 1 , null from dual union all
    select 2 , 1    from dual union all
    select 3 , 2    from dual union all
    select 4 , 1    from dual
)
,FAVORITES( pk, fk_user, fk_tree_node) as (
    select 0, 0, 1 from dual
)
,v_tree as (
    select pk,pk_parent_node,sys_connect_by_path(pk,'/') p_path
    from TREE
    start with pk_parent_node is null
    connect by prior pk = pk_parent_node
)
select *
from v_tree;

结果：

        PK PK_PARENT_NODE P_PATH
---------- -------------- ------------------------------
         1 NULL           /1
         2              1 /1/2
         3              2 /1/2/3
         4              1 /1/4

事实上，您已经可以检查是否P_PATH包含收藏节点，但由于您想要查看用户，我们可以将它们聚合到一个新列中：

with 
TREE(pk, pk_parent_node) as (
    select 1 , null from dual union all
    select 2 , 1    from dual union all
    select 3 , 2    from dual union all
    select 4 , 1    from dual
)
,FAVORITES( pk, fk_user, fk_tree_node) as (
    select 0, 0, 1 from dual union all
    select 1, 1, 3 from dual
)
,v_tree as (
    select pk,pk_parent_node,sys_connect_by_path(pk,'/')||'/' p_path
    from TREE
    start with pk_parent_node is null
    connect by prior pk = pk_parent_node
)
select 
   v.*, v2.*
from v_tree v
     outer apply(
        select 
           xmlelement(USERS, xmlagg(xmlelement(ID, f.pk) order by f.pk)) as users
        from FAVORITES f
        where p_path like '%/'||f.fk_tree_node||'/%'
     ) v2;

结果：

       PK PK_PARENT_NODE P_PATH                         USERS
---------- -------------- ------------------------------ ------------------------------------------------------------
         1 NULL           /1/                            <USERS><ID>0</ID></USERS>
         2              1 /1/2/                          <USERS><ID>0</ID></USERS>
         3              2 /1/2/3/                        <USERS><ID>0</ID><ID>1</ID></USERS>
         4              1 /1/4/                          <USERS><ID>0</ID></USERS>

为了更清楚，我又添加了一个用户 1。

所以现在你只需要添加一个谓词来过滤用户：

with 
TREE(pk, pk_parent_node) as (
    select 1 , null from dual union all
    select 2 , 1    from dual union all
    select 3 , 2    from dual union all
    select 4 , 1    from dual
)
,FAVORITES( pk, fk_user, fk_tree_node) as (
    select 0, 0, 1 from dual union all
    select 1, 1, 3 from dual
)
,v_tree as (
    select pk,pk_parent_node,sys_connect_by_path(pk,'/')||'/' p_path
    from TREE
    start with pk_parent_node is null
    connect by prior pk = pk_parent_node
)
,v_final_view as (
    select 
       v.*, v2.*
    from v_tree v
         outer apply(
            select 
               xmlelement(USERS, xmlagg(xmlelement(ID, f.pk) order by f.pk)) as users
            from FAVORITES f
            where p_path like '%/'||f.fk_tree_node||'/%'
         ) v2
)
select *
from v_final_view
where 
   xmlexists(
      '$USERS/USERS[ID=$USER_ID]' 
      passing 
        users as USERS, 
        1 as USER_ID -- your input param - user id
    )
;

结果：

  PK PK_PARENT_NODE P_PATH       USERS
---- -------------- ------------ ------------------------------------
   3              2 /1/2/3/      <USERS><ID>0</ID><ID>1</ID></USERS>

当然，这只是这种方法的一个示例，因此您可以使用其他函数进行聚合，甚至可以创建物化视图以提高性能。

推荐阅读