查找第一个满足条件sql server的父级

发布于 2024-12-17 01:25:43 字数 1463 浏览 0 评论 0原文

所以我面临着一个有点困难的问题,至少对我来说是这样。

想象一下,我正在处理包含分支、区域等的不那么虚构的层次结构数据。我有一些使用存储过程中的数据的代码,但最近在出现循环时遇到了问题。

为了让您了解我的过程消耗的数据是什么样的:

Branch 12K3 Parent 12K
Branch 12K2 Parent 12K
Branch 12K  Parent REGION AA123
....

我的工作是创建新节点并从上述数据中分配适当的父节点。有点像下面这样。

CPX Node 1 Parent REGION AA456
CPX Node 2 Parent REGION SomeOtherRegion
CPX Node 3 Parent 12K2
CPX Node 4 Parent 12K

请注意,我有两个节点,它们的父节点恰好是分支。这是不希望的。

目标:对于父级为 Branch 的任何 CPX 节点,沿着层次结构向上移动,直到找到恰好是 Region 的第一个节点。

所以在这种情况下,我的节点应该看起来像这样,

CPX Node 1 Parent REGION AA456
CPX Node 2 Parent REGION SomeOtherRegion
CPX Node 3 Parent AA123 -- previously 12K2
CPX Node 4 Parent AA123 -- previously 12K

我在 2005 年对整个递归 CTE 事物很陌生,所以我天真地尝试了这个:

;with cte as
(
    -- initialization -- don't worry, this just checks for a cycle condition
    select t1.SourceID, t1.SourceParentID
    from @someTable t1
    inner join @someTable t2
    on t1.SourceID = t2.SourceParentID
    and t2.SourceID = t1.SourceParentID

    union all

    -- recursive execution
    select p.SourceID, gp.SourceParentID
    from cte p
    inner join entities as gp with (nolock)
    on gp.SourceID = p.SourceParentID
)
select * from cte

这可行,但递归永远不会终止。如果比我更有经验的人可以告诉我我做错了什么,我将非常感激!

我的 SQL 的输出:

IMG1

So I'm faced with a bit of a difficult problem, at least for me.

Imagine I'm working with not-so-fictional hierarchy data that contains branches, regions, etc. I've got some code that consumes data from a stored procedure, but recently ran into a problem when a cycle appeared.

To give you a feel for what the data my procedure consumes looks like:

Branch 12K3 Parent 12K
Branch 12K2 Parent 12K
Branch 12K  Parent REGION AA123
....

My job is to create new nodes and assign the appropriate parent from the above data. Sort of like below.

CPX Node 1 Parent REGION AA456
CPX Node 2 Parent REGION SomeOtherRegion
CPX Node 3 Parent 12K2
CPX Node 4 Parent 12K

Notice I have two nodes that have parents which happen to be a Branch. This is not desired.

Goal: For any CPX node whose parent is a Branch, walk up the hierarchy until we find the first node which happens to be a Region.

So in this case my nodes should look like this

CPX Node 1 Parent REGION AA456
CPX Node 2 Parent REGION SomeOtherRegion
CPX Node 3 Parent AA123 -- previously 12K2
CPX Node 4 Parent AA123 -- previously 12K

I'm new at this whole recursive CTE thing in 2005, so I naively tried this:

;with cte as
(
    -- initialization -- don't worry, this just checks for a cycle condition
    select t1.SourceID, t1.SourceParentID
    from @someTable t1
    inner join @someTable t2
    on t1.SourceID = t2.SourceParentID
    and t2.SourceID = t1.SourceParentID

    union all

    -- recursive execution
    select p.SourceID, gp.SourceParentID
    from cte p
    inner join entities as gp with (nolock)
    on gp.SourceID = p.SourceParentID
)
select * from cte

This would work but the recursion never terminates. If someone with more experience than me could tell me what I'm doing wrong, I'd really appreciate it!

Output of my SQL:

IMG1

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

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文