sql - 具有层次结构级别的递归 cte sql

Question

我对这个递归 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 8 级别 1

Answer 1

您还没有告诉我们您如何知道用户是否拥有给定 ID 的权限。这是一条必要的信息。我将在下面放置一些代码，假设您向查询中添加了一个名为 hasRights 的列，如果用户没有权限，则该列的值将为零，如果用户有权限，则值为 1。您可能需要对此进行调整，因为我没有要测试的数据，但希望它能让您接近。
基本上，如果用户具有权限，则查询被更改为仅将级别加 1。如果用户有权限，它也只会添加到排序路径，否则会附加一个空字符串。因此，如果 ids 8 和 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