sql - 递归 CTE 在 SQL Server 中查找层次结构中的第一个管理器
问题描述
我有一个用户表
我想通过他们的工作位置层次结构为每个用户找到他们的第一个经理。
我设法编写了这个脚本,但我似乎无法让它正常工作。我需要用户 1003 必须有 1001 managerId。现在它显示 1002。
select 1001 as userid, 'L1' as locationCode, NULL as parentLocationCode, 1 as isManager into #Users union all
select 1002 as userid, 'L2' as locationCode, 'L1' as parentPad, 0 as isManager union all
select 1003 as userid, 'L3' as locationCode, 'L2' as parentPad, 0 as isManager
;WITH cte_org AS (
SELECT
d.userid
,d.locationCode
,d.parentLocationCode
,d.isManager
,null as managerId
FROM
#Users as d
WHERE d.parentLocationCode is NULL
UNION ALL
SELECT
d.userid
,d.locationCode
,d.parentLocationCode
,d.isManager
,o.userid as managerId
FROM
#Users as d
inner JOIN cte_org o on d.parentLocationCode=o.locationCode
)
SELECT *
FROM cte_org
OPTION (MAXRECURSION 32767);
解决方案
您只需使用 userId 为 manager 设置 managerId,然后将 managerId 复制到链接记录
select 1001 as userid, 'L1' as locationCode, NULL as parentLocationCode, 1 as isManager into #Users union all
select 1002 as userid, 'L2' as locationCode, 'L1' as parentPad, 0 as isManager union all
select 1003 as userid, 'L3' as locationCode, 'L2' as parentPad, 0 as isManager
;
WITH
cte_org AS (
SELECT
d.userid
,d.locationCode
,d.parentLocationCode
,d.isManager
,d.userid as managerId
FROM
#Users as d
WHERE d.parentLocationCode is NULL
UNION ALL
SELECT
d.userid
,d.locationCode
,d.parentLocationCode
,d.isManager
,o.managerId as managerId
FROM
#Users as d
inner JOIN cte_org o on d.parentLocationCode=o.locationCode
)
SELECT *
FROM cte_org
OPTION (MAXRECURSION 32767);
userid locationCode parentLocationCode isManager managerId
1001 L1 1 1001
1002 L2 L1 0 1001
1003 L3 L2 0 1001
推荐阅读
- kubernetes - 为什么clusterip(iptables)的性能这么差?是不是我的配置有问题
- c# - .net core 3.1 无法删除 cookie
- google-app-engine - 从 Angular 访问受 Google IAP 保护的 API
- string - DBFlow 丢失了 String 类型的数据
- amazon-web-services - 有没有办法使用 AWS 流量镜像功能记录生产服务器流量并在舞台环境中重放相同的流量
- javascript - 使用引导程序 4 选项卡时触发单击不起作用
- kotlin - 如何从具有特定属性的列表中获取“集合集”的计数
- javascript - 删除输入值时不会触发 onChange
- android - 更新 OneSignal 依赖项获取依赖项失败错误
- android - Gson 无法解析日期