---
# System prepended metadata

title: sql 遞迴 找 子層樹狀結構
tags: [資料庫, 程式設計]

---

# sql 遞迴 找 子層樹狀結構
###### tags: `程式設計` `資料庫`

## 實際使用的舉例
```sql=
select * from FindTreeNodeChildren('E54DB875-66C7-4C7B-8C3E-1A235006D8DD')
```

```sql=

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Not
-- Create date: 20191029
-- Description:	find children node from tree structure 
-- =============================================
CREATE FUNCTION [dbo].[FindTreeNodeChildren]
(	
	@PageID uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
with ChildrenResult([ParentID],[ID],[Name],lvl)
as(
--Anchor查詢
select [ParentID],[ID],[Name],1 as lvl
from [dbo].[TreeNode] where [ID]=@PageID
union all
--Recursive查詢
select a.[ParentID],a.[ID],a.[Name],lvl+1
from [dbo].[TreeNode] a,ChildrenResult b
where a.[ParentID]=b.[ID]
)
--執行CTE查詢
select [ParentID],a.[ID],[Name],b.body,lvl as lvl
from ChildrenResult as a,CustomPage as b
where  a.ID = b.page_id  and b.status ='P' 

)

GO
```

## 複製父層結構
https://stackoverflow.com/questions/2830195/use-sql-to-clone-a-tree-structure-represented-in-a-database
```sql=
WITH q AS
(
    SELECT  h.*, 1 AS level
    FROM    Table1 h
    WHERE   id = 3
    UNION ALL
    SELECT  hp.*, level + 1
    FROM    q
    JOIN    Table1 hp
    ON      hp.id = q.ParentGroupID
), q2 AS (
    SELECT
        ID,
        ParentGroupID,
        SomeValue,
        (SELECT MAX(level) FROM q) - level AS level
    FROM q
)
INSERT INTO table1
SELECT
    (SELECT MAX(ID) FROM Table1) + level + 1 AS ID,
    CASE WHEN level = 0 THEN -1
         ELSE (SELECT MAX(ID) FROM Table1) + level
    END AS ParentGroupID,
    SomeValue + '-cloned'
FROM    q2
```
## 複製子層結構
sample data
```sql=
DECLARE @Items TABLE (
   ITEMID               int                  identity,
   MODELID              int                  not null,
   PARENT               int                  null,
   ITEMNUM              nvarchar(20)         not null
)
INSERT INTO @Items (MODELID, PARENT, ITEMNUM) VALUES
(1, Null, 'A'),
(1, Null, 'B'),
(1, Null, 'C'),
(1,   1 , 'A.A'),
(1,   2 , 'B.B'),
(1,   4 , 'A.A.A'),
(1,   4 , 'A.A.B'),
(1,   3 , 'C.A'),
(1,   3 , 'C.B'),
(1,   9 , 'C.B.A');
```
I omit the code that duplicates the Model row. Eventually you'll have ID of original Model and new Model.
```sql=
DECLARE @SrcModelID int = 1;
DECLARE @DstModelID int = 2;
```
Declare a table variable (or temp table) to hold the mapping between old and new item IDs.
```sql=
DECLARE @T TABLE(OldItemID int, NewItemID int);
```
把要複製的資料copy一份，放到暫存資料表，這時候的父節點還是舊的，但ID已經是新的了

Make a copy of Items remembering the mapping of IDs in the table variable and keeping old PARENT values.

```sql=
MERGE INTO @Items
USING
(
    SELECT ITEMID, PARENT, ITEMNUM
    FROM @Items AS I
    WHERE MODELID = @SrcModelID
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (MODELID, PARENT, ITEMNUM)
VALUES
    (@DstModelID
    ,Src.PARENT
    ,Src.ITEMNUM)
OUTPUT Src.ITEMID AS OldItemID, inserted.ITEMID AS NewItemID
INTO @T(OldItemID, NewItemID);
```

Update old PARENT values with new IDs

```sql=
WITH
CTE
AS
(
    SELECT I.ITEMID, I.PARENT, T.NewItemID
    FROM
        @Items AS I
        INNER JOIN @T AS T ON T.OldItemID = I.PARENT
    WHERE I.MODELID = @DstModelID
)
UPDATE CTE
SET PARENT = NewItemID;
```
Check the results
```sql=
SELECT * FROM @Items;
```

![](https://i.imgur.com/Dh21pzC.png)

https://stackoverflow.com/questions/33822343/make-a-copy-of-parent-child-structure-in-sql

## where in + 子查詢
```=sql
-- 子查詢取一堆ID
SELECT ID, ParentID, [Name]
FROM TreeNodeTest AS I
WHERE ID in (select ID from ChildrenResult)
```

## where ColumnName in 在linq大概是這樣用 
```=sql
// where ColumnName in  在linq大概是這樣用 
// https://blogs.msdn.microsoft.com/alexj/2009/03/25/tip-8-how-to-write-where-in-style-queries-using-linq-to-entities/
var matches = ctx.People.Where( 
        BuildOrExpression<People, string>( 
           p => p.Firstname, names 
        ) 
);
```

## sql中產生guid的方式
```=sql
-- sql中產生guid的方式
DECLARE @GUID uniqueidentifier = NEWID();
DECLARE @GUID2 uniqueidentifier = 'A972C577-DFB0-064E-1189-0154C99310DAAC12';

select @GUID,@GUID2
```

## temptable 
```=sql
-- @或#符號在資料表前面是 temptable  https://dotblogs.com.tw/daniel/2018/01/19/174836
-- @是寫在memory，#或##是寫在disk的，#系列用完要記得刪掉
SELECT * into #TempTable from ChildrenResult
SELECT * FROM #TempTable;
drop table  #TempTable
```

## insert into select，把選取的結果insert到另一張資料表
```
-- insert into select，把選取的結果insert到另一張資料表
insert into TreeNodeTest select [page_id] as ID, null as ParentID ,title as [Name]  from [MJIBMVC].[dbo].[CustomPage]  where title = '某個title'
```

## 只想要排除特定一個欄位的select
```
-- 只想要排除特定一個欄位的select
-- https://stackoverflow.com/questions/29095281/how-to-select-all-the-columns-of-a-table-except-one-column/29114381
SELECT * INTO #TemporaryTable FROM YourTableName
ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove
SELECT * FROM #TemporaryTable 
DROP TABLE #TemporaryTable 
```

## 尋找子層 可代入變數版
測試用的 sql 語法
```=sql
exec FindTreeNodeChildrenById '[dbo].[TreeNodeTest]','[ParentID]','[ID]','[Name]','''7d22f6f2-06fe-4cd9-80b8-709a55a11261''';
```
建立用的sql語法
```=sql

/****** Object:  Table [dbo].[TreeNodeTest]    Script Date: 2019/10/5 下午 03:19:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TreeNodeTest](
	[ID] [uniqueidentifier] NOT NULL,
	[ParentID] [uniqueidentifier] NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TreeNodeTest] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[TreeNodeTest] ([ID], [ParentID], [Name]) VALUES (N'4c4d14da-c6ba-45fb-a09a-041af8875d2e', N'5a2be688-a50e-497d-91c2-517612cdd121', N'AA')
GO
INSERT [dbo].[TreeNodeTest] ([ID], [ParentID], [Name]) VALUES (N'6aba891d-fb91-471c-9450-3a1a5a3d8dad', N'7d22f6f2-06fe-4cd9-80b8-709a55a11261', N'BA')
GO
INSERT [dbo].[TreeNodeTest] ([ID], [ParentID], [Name]) VALUES (N'3216faa9-261e-4320-b60e-5144f2c985b5', NULL, N'測試頁面')
GO
INSERT [dbo].[TreeNodeTest] ([ID], [ParentID], [Name]) VALUES (N'5a2be688-a50e-497d-91c2-517612cdd121', NULL, N'A')
GO
INSERT [dbo].[TreeNodeTest] ([ID], [ParentID], [Name]) VALUES (N'7d22f6f2-06fe-4cd9-80b8-709a55a11261', NULL, N'B')
GO
INSERT [dbo].[TreeNodeTest] ([ID], [ParentID], [Name]) VALUES (N'd9668347-f20f-47c0-83d2-750e488e7180', N'5a2be688-a50e-497d-91c2-517612cdd121', N'AB')
GO
INSERT [dbo].[TreeNodeTest] ([ID], [ParentID], [Name]) VALUES (N'cd7131be-52a5-4b25-80c8-ff6eee3e57f3', N'7d22f6f2-06fe-4cd9-80b8-709a55a11261', N'BB')
GO
ALTER TABLE [dbo].[TreeNodeTest] ADD  CONSTRAINT [DF_TreeNodeTest_ID]  DEFAULT (newid()) FOR [ID]
GO
ALTER TABLE [dbo].[TreeNodeTest]  WITH CHECK ADD  CONSTRAINT [FK_TreeNodeTest_TreeNodeTest] FOREIGN KEY([ParentID])
REFERENCES [dbo].[TreeNodeTest] ([ID])
GO
ALTER TABLE [dbo].[TreeNodeTest] CHECK CONSTRAINT [FK_TreeNodeTest_TreeNodeTest]
GO
/****** Object:  StoredProcedure [dbo].[FindTreeNodeChildrenById]    Script Date: 2019/10/5 下午 03:19:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[FindTreeNodeChildrenById]
	-- Add the parameters for the stored procedure here
	@TableName NVARCHAR(MAX) ='[dbo].[TreeNode]',
	@ParentID NVARCHAR(MAX) ='[ParentID]',
	@ID NVARCHAR(MAX) ='[ID]',
	@Name NVARCHAR(MAX) ='[Name]',
	@PageID NVARCHAR(MAX) = '''5a2be688-a50e-497d-91c2-517612cdd121'''
AS
BEGIN
 DECLARE @query NVARCHAR(MAX);
 DECLARE @WhereID NVARCHAR(MAX);

 IF @PageID=''
	 set @WhereID = '';
 else
	 set @WhereID = N'where ' +@ID +'=' +@PageID +''


set @query = N'

with ChildrenResult(' +@ParentID +',' +@ID +',' +@Name +',lvl)
as(
select ' +@ParentID +',' +@ID +',' +@Name +',1 as lvl
from '+@TableName+' 
'+@WhereID+'
union all

select a.' +@ParentID +',a.' +@ID +',a.' +@Name +',lvl+1
from '+@TableName+' a,ChildrenResult b
where a.' +@ParentID +'=b.' +@ID +'
)

select ' +@ParentID +', a.' +@ID +' as page_id, ' +@Name +' as title,lvl as lvl
from ChildrenResult as a
   ';

EXECUTE sp_executesql  @query;

END
GO

```