BOM 2 ---------- deep sorting

-- Test Data deep sorting
DECLARE @ t TABLE (ID char (3), PID char (3), Name nvarchar (10 ))
INSERT @ t SELECT '001', NULL, 'shandong province'
Union all select '002', '001', 'yantai City'
Union all select '004 ', '002', 'zhaoyuan City'
Union all select '003 ', '001', 'qingdao'
Union all select '005 ', NULL, 'sihui City'
Union all select '006 ', '005', 'qingyuan City'
Union all select '007 ', '006', 'sub-city'

-- Display in deep sorting
-- Generate the total number of codes for each node (same as the single-number encoding)
DECLARE @ t_Level TABLE (ID char (2), Level int, Sort varchar (8000 ))
DECLARE @ Level int
SET @ Level = 0
INSERT @ t_Level select id, @ Level, ID
FROM @ t
WHERE PID IS NULL
WHILE @ ROWCOUNT> 0
BEGIN
SET @ Level = @ Level + 1
INSERT @ t_Level SELECT a. ID, @ Level, B. Sort + a. ID
FROM @ t a, @ t_Level B
WHERE a. PID = B. ID
AND B. Level = @ Level-1
END

-- Display Results
SELECT .*
FROM @ t a, @ t_Level B
WHERE a. ID = B. ID
Order by B. Sort
/* -- Result
Id pid Name
-------------------------
001 NULL Shandong Province
002 001 Yantai City
004 002 Zhaoyuan city
003 001 Qingdao
005 NULL sihui City
006 005 Qingyuan City
007 006 sub-city
--*/

-- Query functions of a specified node and all its subnodes
Create function f_Cid (@ ID char (3 ))
RETURNS @ t_Level TABLE (ID char (3), Level int)
AS
BEGIN
DECLARE @ Level int
SET @ Level = 1
INSERT @ t_Level SELECT @ ID, @ Level
WHILE @ ROWCOUNT> 0
BEGIN
SET @ Level = @ Level + 1
INSERT @ t_Level SELECT a. ID, @ Level
FROM tb a, @ t_Level B
WHERE a. PID = B. ID
AND B. Level = @ Level-1
END
RETURN
END
GO

-- Call the function to query 002 and all its subnodes
SELECT .*
FROM tb a, f_Cid ('002') B
WHERE a. ID = B. ID
/* -- Result
Id pid Name
-----------------------
002 001 Yantai City
004 002 Zhaoyuan city
--*/

 

-- Test Data
Declare @ t table (ID char (3), PID char (3), name nvarchar (10 ))
Insert @ t select '001', null, 'shandong province'
Union all select '002', '001', 'yantai City'
Union all select '004 ', '002', 'zhaoyuan City'
Union all select '003 ', '001', 'qingdao'
Union all select '005 ', null, 'sihui City'
Union all select '006 ', '005', 'qingyuan City'
Union all select '007 ', '006', 'sub-city'

-- Display in deep sorting
-- Generate the total number of codes for each node (same as the single-number encoding)
Declare @ t_level table (ID char (2), Level int, sort varchar (8000 ))
Declare @ level int
Set @ level = 0
Insert @ t_level select ID, @ level, ID
From @ t
Where PID is null
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
INSERT @ t_Level SELECT a. ID, @ Level, B. Sort + a. ID
FROM @ t a, @ t_Level B
WHERE a. PID = B. ID
AND B. Level = @ Level-1
END

-- Display Results
Select space (B. Level * 2) + '| --' + a. Name
FROM @ t a, @ t_Level B
WHERE a. ID = B. ID
Order by B. Sort
/* -- Result
| -- Shandong Province
| -- Yantai City
| -- Zhaoyuan city
| -- Qingdao
| -- Sihui City
| -- Qingyuan City
| -- Sub-city
--*/