如何在 SQL Server 中使用公共表表达式并检查不重复

Question

我有一个对其自身的引用。

用户表：id、username、managerid 和 managerid 链接回 id

现在，我想获取所有经理，包括直接经理、经理直接管理器，等等......问题是我不想有一个不间断的递归sql。

所以，我想检查一个 id 是否已经在列表中，我将不再包含它。

这是我的 sql：

with        
    all_managers (id, username, managerid, idlist) as
    (
        select u1.id, u1.username, u1.managerid, ' '
        from users u1, users u2
        where u1.id = u2.managerid
        and u2.id = 6

        UNION ALL

        select u.id, u.username, u.managerid, idlist + ' ' + u.id
        from all_managers a, users u
        where a.managerid = u.id
        and charindex(cast(u.id as nvarchar(5)), idlist) != 0
    )   
select id, username
from all_managers;

问题是在这一行中：

select u1.id, u1.username, u1.managerid, ' '

SQL Server 向我抱怨我不能将 ' ' 作为 idlist 的初始化。 nvarchar(40) 也不起作用。我不知道如何在像这样的公共表表达式中声明它。通常，在 db2 中，我可以只放入 varchar(40)

我的示例数据：

ID  UserName  ManagerID

1   admin     1
2   a         1
3   b         1
4   c         2

我想做的是找到 c Guy 的所有经理。结果应该是： 管理员，a，b。

有些用户可以是他的经理（如 admin），因为 ManagerID 不允许为 NULL，而有些用户没有直接经理。

使用公共表表达式，可能会导致无限递归。因此，我也试图通过尝试不包含两次 id 来避免这种情况。例如，在第一次迭代中，我们已经有了 id : 1，因此，在第二次迭代及以后的迭代中，决不应该允许使用 1 。

我还想问一下我现在的做法好不好，还有其他解决办法吗？因为如果我有一个具有很深层次结构的大型数据库，我将必须初始化一个大的 varchar 来保留它，并且它会消耗内存，对吗？

Answer 1

解决铸造问题很容易。您只需将联合中的两个值转换为相同的类型：

With
    all_managers (id, username, managerid, idlist) as
    (
        Sselect u1.id, u1.username, u1.managerid
            , Cast(' ' As varchar(40))
        From users As u1
            Cross Join users As u2
        Where u1.id = u2.managerid
            and u2.id = 6
        Union All
        Select u.id, u.username, u.managerid
            , Cast(Coalesce(idlist + ' ','') + Cast(u.id As varchar(10)) As varchar(40))
        From all_managers As a
            Cross Join users As u
        Where a.managerid = u.id
            and CharIndex(Cast(u.id as nvarchar(5)), idlist) != 0
    )   
Select id, username, idlist
From all_managers;

但是，我仍然不太确定您想要实现的目标。您能否向我们展示一些示例数据，说明结果集中应该返回什么以及不应该返回什么？