如何使用公用表表达式并检查SQL Server中是否没有重复项 [英] How to use Common Table Expression and check no duplication in SQL Server

问题描述

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

I have a table references to itself.

用户表： id，用户名，managerid 和 managerid 链接回 id

现在，我想获取所有经理（包括直接经理，直接经理的经理等等）……问题是我不想拥有不间断的递归sql。

Now, I want to get all the managers including direct manager, manager of direct manager, so on and so forth... The problem is that I do not want to have a unstop recursive sql.

因此，我想检查列表中是否存在id别名，因此不再赘述。

So, I want to check if an id alreay in a list, I will not include it anymore.

这是我的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）

The SQL Server complains with me that I can not put ' ' as the initialized for idlist. nvarchar(40) does not work as well. I do not know how to declare it inside a common table expression like this one. Usually, in db2, I can just put varchar(40)

我的样本数据：

ID  UserName  ManagerID

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

我想要做的是我想找到所有c家伙的经理。结果应该是：
admin，a，b。

What I want to do is that I want to find all managers of c guy. The result should be: admin, a, b.

由于ManagerID不允许NULL，并且某些用户可以成为其管理员（例如admin）。一些没有直接经理。

Some of the user can be his manager (like admin) because the ManagerID does not allow NULL and some does not have direct manager.

使用公用表表达式，可以导致无限递归。因此，我也尝试通过不两次包含ID来避免这种情况。例如，在第一次迭代中，我们已经有id：1，因此，在第二次迭代中，以后将永远不允许1。

With common table expression, it can lead to an infinite recursive. So, I am also trying to avoid that situation by trying to not include the id twice. For example, in the 1st iteration, we already have id : 1, so, in the 2nd iteration and later on, 1 should never be allowed.

我也想请问我目前的方法是否好，还有其他解决方案吗？因为如果我有一个具有深层次结构的大型数据库，则必须初始化一个大型varchar才能保留它并消耗内存，对吗？

I also want to ask if my current approach is good or not and any other solutions? Because if I have a big database with a deep hierarchy, I will have to initialize a big varchar to keep it and it consumes memory, right?

推荐答案

解决投放问题很容易。您只需要将联合中的两个值都强制转换为相同的类型：

Solving the casting issue is easy enough. You simply need to cast both values in the union to the same type:

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;

但是，我仍然不太确定您要达到的目标。您能否向我们显示一些应在结果集中返回的样本数据？

However, I'm still not quite sure what you are trying to achieve. Can you show us some sample data of what should and should not be returned in the resultset?

这篇关于如何使用公用表表达式并检查SQL Server中是否没有重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！