# 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://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
```
Sign in with Wallet
Connect another wallet