1. 首页
  2. 问题
  3. 具有层次结构级别的递归CTE SQL
一尘不染

具有层次结构级别的递归CTE SQL

sql

我对该递归CTE有点问题，它工作正常，除非当我的用户没有root可读权限时，意味着该元素没有条目。因此，如果我仅对具有权限的用户运行此查询，而该用户仅在树内的叶子上运行，则此查询的级别部分将无法正常工作。

它将显示例如6的真实 级别 层次结构，但对于他来说，它是最上面的第一可读元素，因此应为1。

WITH Tree
AS (
SELECT
    id,
    parent,
    0 AS Level,
    id AS Root,
    CAST(id AS VARCHAR(MAX)) AS Sort,
    user_id
FROM SourceTable
WHERE parent IS NULL

UNION ALL

SELECT 
    st.id,
    st.parent,
    Level + 1 AS Level,
    st.parent AS Root,
    uh.sort + '/' + CAST(st.id AS VARCHAR(20)) AS Sort,
    st.user_id
FROM SourceTable AS st
    JOIN Tree uh ON uh.id = st.parent    
)

SELECT * FROM Tree AS t
    JOIN UserTable AS ut ON  ut.id = t.user_id AND ut.user_id = '141F-4BC6-8934'
ORDER BY Sort

级别如下

id  level
 5    0
 2    1
 7    2
 4    2
 1    2
 6    1
 3    2
 8    2
 9    3

当用户现在仅具有ID 8和9的读取权限时，CTE的ID级别8保持为2，ID 9级别为3，但是如果之前没有ID，则我需要ID 8级别1


阅读 138

收藏
2021-05-23

共1个答案

一尘不染

您尚未告诉我们如何知道用户是否拥有给定ID的权限。这是必要的信息。我将在下面放置一些代码，假设您向查询中添加了一个名为 hasRights
的列，并且如果用户没有权限，则该列的值为零，如果拥有权限，则该列的值为1。您可能需要对此进行调整，因为我没有可以测试的数据，但希望它能使您接近。

基本上，将查询更改为仅在用户具有权限的情况下才向级别加1。如果用户拥有权限，它也只会添加到排序路径，否则会附加一个空字符串。因此，如果ID 8和ID
9是用户只能访问的项目，则应该看到级别1和2并对类似于“ 5/8/9”而不是“
5/6/8/9”的路径进行排序。如果您仍然无法使它正常工作，那么如果您在SqlFiddle上发布示例架构，那将对我们有很大帮助。

WITH Tree
AS (
SELECT
    id,
    parent,
    0 AS Level,
    id AS Root,
    hasRights AS HasRights,
    CAST(id AS VARCHAR(MAX)) AS Sort,
    user_id
FROM SourceTable
WHERE parent IS NULL

UNION ALL

SELECT 
    st.id,
    st.parent,
    Level + st.hasRights AS Level,
    st.parent AS Root,
    st.hasRights AS HasRights,
    uh.sort + CASE st.hasRights WHEN 0 THEN '' ELSE '/' + CAST(st.id AS VARCHAR(20)) END AS Sort,
    st.user_id
FROM SourceTable AS st
    JOIN Tree uh ON uh.id = st.parent    
)

SELECT * FROM Tree AS t
    JOIN UserTable AS ut ON  ut.id = t.user_id AND ut.user_id = '141F-4BC6-8934'
ORDER BY Sort
2021-05-23