使用 Sql Server 2005 过滤自下而上的递归 CTE

Question

我试图从下往上查询单个数据库表中的数据层次结构（我不想包括由于权限而没有特定类型孩子的父母）。架构和示例数据如下：

create table Users(
id int,
name varchar(100));

insert into Users values (1, 'Jill');

create table nodes(
    id int,
    name varchar(100),
    parent int,
    nodetype int);

insert into nodes values (1, 'A', 0, 1);
insert into nodes values (2, 'B', 0, 1);
insert into nodes values (3, 'C', 1, 1);
insert into nodes values (4, 'D', 3, 2);
insert into nodes values (5, 'E', 1, 1);
insert into nodes values (6, 'F', 5, 2);
insert into nodes values (7, 'G', 5, 2);

create table nodeAccess(
    userid int,
    nodeid int,
    access int);

insert into nodeAccess values (1, 1, 1);
insert into nodeAccess values (1, 2, 1);
insert into nodeAccess values (1, 3, 1);
insert into nodeAccess values (1, 4, 1);
insert into nodeAccess values (1, 5, 1);
insert into nodeAccess values (1, 6, 0);
insert into nodeAccess values (1, 7, 1);


with Tree(id, name, nodetype, parent)
as
(
    select n.id, n.name, n.nodetype, n.parent
    from nodes as n
    inner join nodeAccess as na on na.nodeid = n.id
    where na.access =1 and na.userid=1 and n.nodetype=2

    union all

    select n.id, n.name, n.nodetype, n.parent
    from nodes as n
    inner join Tree as t on t.parent = n.id
    inner join nodeAccess as na on na.nodeid = n.id
    where na.access =1 and na.userid=1 and n.nodetype=1
)
select * from Tree

产量：

  id    name    nodetype    parent
   4    D        2             3
   7    G        2             5
   5    E        1             1
   1    A        1             0
   3    C        1             1
   1    A        1             0

如何才能在结果集中不包含重复项？针对实际表的查询在最低级别有更多的节点，因此父节点有更多的重复项。该解决方案至少需要与 SQL Server 2005 配合使用。

提前致谢！

Answer 1

最简单（不一定是最有效）的解决方案：

...
)
SELECT DISTINCT id,name,nodetype,parent FROM Tree;

这会更改示例输出的顺序，因为 DISTINCT 运算符实现排序。如果那里有一些故意排序，我无法检测到它，但如果您知道所需的顺序，则可以添加 ORDER BY 。