关于Join
文章目录

SQL Server: uuid in CTE recursive

CTE 递归时遇到问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

WITH TopLayer AS (

SELECT newID() as ID, null as parentID
FROM Item

), ItemTree AS (

select
ID,
parentID,
newID,
newParentID
from TopLayer

UNION ALL

select
child.ID,
child.parentID,
newID() as newID,
parent.newID as newParentID
from surveyPartnerGroupComponent child WITH (NOLOCK)
inner join ItemTree Parent
on child.parentID = Parent.ID
)

newID() 放在 CTE 里面并且进行递归会有副作用
导致下方每一次 select from TopLayer 都会生成一个新的 uuid
解决方法很简单: 将 newID() 放到 CTE 外面

解决方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

SELECT newID() as ID, null as parentID
INTO #Item
FROM Item

WITH TopLayer AS (

SELECT *
FROM #Item

), ItemTree AS (

select
ID,
parentID,
newID,
newParentID
from TopLayer

UNION ALL

select
child.ID,
child.parentID,
newID() as newID,
parent.newID as newParentID
from surveyPartnerGroupComponent child WITH (NOLOCK)
inner join ItemTree Parent
on child.parentID = Parent.ID
)

或者还可以更加精简一些:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT newID() as ID, null as parentID
INTO #Item
FROM Item

WITH ItemTree AS (

select
ID,
parentID,
newID,
newParentID
from TopLayer

UNION ALL

select
child.ID,
child.parentID,
newID() as newID,
parent.newID as newParentID
from surveyPartnerGroupComponent child WITH (NOLOCK)
inner join #Item Parent
on child.parentID = Parent.ID
)