在sql server中有两个表的递归CTE [英] recursive CTE with two tables in sql server
问题描述
我有两张桌子顶级和父母。 parentchild table是彼此相关的树。树看起来像这样。
TREE
1
-11
2
-12
- 13
3
-14
- 15
--- 16
drop table #TopLevel
创建 表 #TopLevel
(
TopLevelID INT ,
createdate DateTime
)
insert into #TopLevel
(TopLevelID,createdate
)
select 1 ,' 2013-03-01 00 :00:00' union all
选择 2 ,' 2013-03-07 00:00:00' union 全部
选择 3 , ' 2013-03-06 00:00:00' union all
选择 4 ,' 2013-03-03 00:00:00' union 所有
选择 5 , ' 2013-03-08 00:00:00' union all
选择 6 ,' 2013-03-09 00:00:00' union 全部
选择 7 ,' 2013-03-10 00:00:00'
drop table #parentchild
create table #parentchild
(
parentchildID INT ,Parent INT ,Child INT
)
插入 进入 #parentchild
(
parentchildID,Parent,Child
)
选择 1 , 1 , 11 union 全部
选择 2 , 12 , 13 union 全部
选择 4 , 15 , 16 union 所有
选择 5 , 14 , 15 union 全部
选择 3 , 2 , 12 union all
选择 6 , 3 , 14
; < span class =code-keyword> with abc as
(
select * 来自 #parentchild
left 外 join #TopLevel on #parentchild.Parent = #TopLevel.TopLevelID
)
选择 * 来自 abc
我需要为#parentchild表中的每一行找到toplevelid。例如,在#parentchild表中,parent = 12不在#toplevel表中,因为它的子项也是如此。那么如果我们看到child = 12且parent是2,那就是#toplevel表。
请帮忙。谢谢。
数据应该在#parentchild表中看起来像这样。 *我手动添加。
parentchildID父子TopLevelID createdate
1 1 11 1 2013-03-01 00: 00:00.000
2 12 13 * 2 * 2013-03-07 00:00:00.000
4 15 16 * 3 * 2013-03-06 00:00:00.000
5 14 15 * 3 * 2013-03-06 00:00:00.000
3 2 12 2 2013-03-07 00:00:00.000
6 3 14 3 2013-03-06 00:00:00.000
我做错了什么?
; abc as
(
选择 ParentChildID,Parent,Child,TopLevelID,CreateDate 来自 #parentchild
left 外部 加入 #TopL evel on #parentchild.Parent = #TopLevel.TopLevelID
)
,xyz as
(
选择 ParentChildID,Parent,Child,TopLevelID,CreateDate 来自 abc 其中 TopLevelID IS NULL
union 所有
选择 a .ParentChildID,a.Parent,a.Child,a.TopLevelID,a.CreateDate 来自 abc a
inner join abc e on e.TopLevelID = a.Parent
)
选择 * 来自 xyz
请阅读我对qu的评论首先。
这里: SQL向导第三部分 - 公用表表达式(CTE) [ ^ ]你会找到获取分层数据的示例代码。
但我认为你的结构是错误的。有关详细信息,请参阅:
请参阅:分层数据(SQL Server) ) [ ^ ]
SQL中的分层和递归查询 [ ^ ]
教程:使用hierarchyid数据类型 [ ^ ]
SQL 2008中具有HierarchyID的层次结构 [ ^ ]
包含公用表表达式的层次结构 [ ^ ]
使用SQL Server 2008的数据层次结构 [ ^ ]
具有公用表表达式的分层查询 [ ^ ]
with c(parentchildid,parent,child,toplevelid,rnk) as (
select
parentchildid,
parent,
child,
parent toplevelid,
1 rnk
来自 parentchild pc
union all
选择
pc。 parentchildid,
pc.parent,
pc.child,
c.toplevelid,
c.rnk + 1 rnk
来自 parentchild pc
join c ON pc.parent = c.child
)
,将排名为(
select parentchildid,parent,child,max(rnk) as rnk
来自 c
group by parentchildid,parent,child
)
选择 c.parentchildid,c.parent,c.child,c.toplevelid,tl.createdate
来自 c
join 排名r
on c.parentchildid = r.parentchildid
和 c.rnk = r.rnk
join toplevel tl
on tl.toplevelid = c.toplevelid
但是当你已经被告知,这不是一个好的设计。
告诉我们你想要达到的目标,我们可能会给你一个更好设计的建议。
I have two tables toplevel and parentchild. parentchild table is a tree which is related to each other. Tree looks like this.
TREE
1
-11
2
-12
--13
3
-14
--15
---16
drop table #TopLevel
create table #TopLevel
(
TopLevelID INT,
createdate DateTime
)
insert into #TopLevel
(TopLevelID,createdate
)
select 1,'2013-03-01 00:00:00' union all
select 2,'2013-03-07 00:00:00' union all
select 3,'2013-03-06 00:00:00' union all
select 4,'2013-03-03 00:00:00' union all
select 5,'2013-03-08 00:00:00' union all
select 6,'2013-03-09 00:00:00' union all
select 7,'2013-03-10 00:00:00'
drop table #parentchild
create table #parentchild
(
parentchildID INT,Parent INT,Child INT
)
insert into #parentchild
(
parentchildID,Parent, Child
)
select 1,1,11 union all
select 2,12,13 union all
select 4,15,16 union all
select 5,14,15 union all
select 3,2,12 union all
select 6,3,14
;with abc as
(
select * From #parentchild
left outer join #TopLevel on #parentchild.Parent=#TopLevel.TopLevelID
)
select * from abc
I need to find toplevelid for each row in #parentchild table. For examble in #parentchild table parent=12 is not in #toplevel table because its child too. then if we see child =12 and parent is 2, that is in #toplevel table.
please help. Thanks.
Data should be look like this in #parentchild table. * one i added manually.
parentchildID Parent Child TopLevelID createdate
1 1 11 1 2013-03-01 00:00:00.000
2 12 13 *2 *2013-03-07 00:00:00.000
4 15 16 *3 *2013-03-06 00:00:00.000
5 14 15 *3 *2013-03-06 00:00:00.000
3 2 12 2 2013-03-07 00:00:00.000
6 3 14 3 2013-03-06 00:00:00.000
Where I am doing wrong?
;with abc as
(
select ParentChildID,Parent,Child,TopLevelID,CreateDate From #parentchild
left outer join #TopLevel on #parentchild.Parent=#TopLevel.TopLevelID
)
,xyz as
(
select ParentChildID,Parent,Child,TopLevelID,CreateDate from abc where TopLevelID IS NULL
union all
select a.ParentChildID,a.Parent,a.Child,a.TopLevelID,a.CreateDate from abc a
inner join abc e on e.TopLevelID=a.Parent
)
select * from xyz
Please, read my comment to the question first.
Here: SQL Wizardry Part Three - Common Table Expressions (CTEs)[^] you'll find sample code to get hierachical data.
But i think your structure is wrong. For further information, please see:
Please refer this: Hierarchical Data (SQL Server)[^]
Hierarchical and recursive queries in SQL[^]
Tutorial: Using the hierarchyid Data Type[^]
Hierarchies with HierarchyID in SQL 2008[^]
Hierarchies WITH Common Table Expressions[^]
Data Hierarchies With SQL Server 2008[^]
Hierarchical Queries with Common Table Expressions[^]
This works:with c(parentchildid,parent,child,toplevelid,rnk) as ( select parentchildid, parent, child, parent toplevelid, 1 rnk from parentchild pc union all select pc.parentchildid, pc.parent, pc.child, c.toplevelid, c.rnk + 1 rnk from parentchild pc join c ON pc.parent = c.child ) ,ranked as ( select parentchildid,parent,child,max(rnk) as rnk from c group by parentchildid,parent,child ) select c.parentchildid,c.parent,c.child,c.toplevelid,tl.createdate from c join ranked r on c.parentchildid = r.parentchildid and c.rnk = r.rnk join toplevel tl on tl.toplevelid = c.toplevelidBut as you have already been told, it's not a good design.
Tell us what you want to achieve and we might give you a suggestion for a better design.
这篇关于在sql server中有两个表的递归CTE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!