递归 CTE - 有限行数

发布于 2024-09-27 22:58:41 字数 883 浏览 4 评论 0原文

我正在尝试在一个非常简单的表（组织）上创建一个查询

，我有列

Organisation, Manager, Superior_Organisation
CEO
Leadership Team, David, CEO 
Production Management, Alex, Leadership Team
Production Site 1, Francoise, Production Management
Production Site 2, Steve, Production Management
Production Site 1 Maintenance, Alan, Production Site 1

....

由于级别不同，我不知道如何创建查询，这为我提供了所有高级组织，从一个开始特定级别

我尝试了此代码的

declare @i int
select @i = 0
-- keep going until no more rows added
while @@rowcount > 0
begin
select @i = @i + 1
-- Get all children of previous level
SELECT     organisations.Organisation, organisations.Manager,  
organisations.Superior_Organisation
FROM         organisations 
end

，但通过此查询，我得到了所有信息，但我不知道如何仅查询上级组织，例如生产站点 1 维护。（可以是 1 或最多 5）

一种方法可能是在表上进行联接，但我认为，这距离高性能还很远。

我见过一些递归CTE查询，但我不熟悉。非常感谢帮助。

原文

I'm trying to create a query on a very simple table (organisations)

I have the columns

Organisation, Manager, Superior_Organisation
CEO
Leadership Team, David, CEO 
Production Management, Alex, Leadership Team
Production Site 1, Francoise, Production Management
Production Site 2, Steve, Production Management
Production Site 1 Maintenance, Alan, Production Site 1

....

Because of different level, I don't know how to create a query, that delivers me all superior organisations, starting at one specific level

I tried this code

declare @i int
select @i = 0
-- keep going until no more rows added
while @@rowcount > 0
begin
select @i = @i + 1
-- Get all children of previous level
SELECT     organisations.Organisation, organisations.Manager,  
organisations.Superior_Organisation
FROM         organisations 
end

But with this query, I get all and I don't know, how I can query only the superior orgs e.g. for Production Site 1 Maintenance. (can be 1 or up to 5)

One way could be a join on the table but I think, that's far away from being performant.

I've seen some recursive CTE queries, but I'm not familiar. So appreciate help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深居我梦 2024-10-04 22:58:42

;WITH organisations AS
(
SELECT 'CEO' AS Organisation, cast(NULL as varchar(50)) AS Manager, cast(NULL as varchar(50)) AS Superior_Organisation UNION ALL
SELECT 'Leadership Team', 'David', 'CEO'  UNION ALL
SELECT 'Production Management', 'Alex', 'Leadership Team' UNION ALL
SELECT 'Production Site 1', 'Francoise', 'Production Management' UNION ALL
SELECT 'Production Site 2', 'Steve', 'Production Management' UNION ALL
SELECT 'Production Site 1 Maintenance', 'Alan', 'Production Site 1'
),
cte As (
SELECT Organisation, Manager, Superior_Organisation
FROM         organisations 
WHERE organisations.Organisation = 'Production Site 1 Maintenance'
UNION ALL
SELECT o.Organisation, o.Manager, o.Superior_Organisation
FROM         organisations o
JOIN cte ON cte.Superior_Organisation = o.Organisation
)
SELECT Organisation, Manager, Superior_Organisation
FROM cte
WHERE Organisation <> 'Production Site 1 Maintenance'

;WITH organisations AS
(
SELECT 'CEO' AS Organisation, cast(NULL as varchar(50)) AS Manager, cast(NULL as varchar(50)) AS Superior_Organisation UNION ALL
SELECT 'Leadership Team', 'David', 'CEO'  UNION ALL
SELECT 'Production Management', 'Alex', 'Leadership Team' UNION ALL
SELECT 'Production Site 1', 'Francoise', 'Production Management' UNION ALL
SELECT 'Production Site 2', 'Steve', 'Production Management' UNION ALL
SELECT 'Production Site 1 Maintenance', 'Alan', 'Production Site 1'
),
cte As (
SELECT Organisation, Manager, Superior_Organisation
FROM         organisations 
WHERE organisations.Organisation = 'Production Site 1 Maintenance'
UNION ALL
SELECT o.Organisation, o.Manager, o.Superior_Organisation
FROM         organisations o
JOIN cte ON cte.Superior_Organisation = o.Organisation
)
SELECT Organisation, Manager, Superior_Organisation
FROM cte
WHERE Organisation <> 'Production Site 1 Maintenance'

回复收藏 0 原文

~没有更多了~