Tree Structure processing _ double numbers

/*
Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel x86) Jul 9 2008 14:43:34 copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation edition on Windows NT 5.1 <x86>
(Build 2600: Service Pack 3)
Hope to make progress together with you
Coincidences
●●●● 17:47:36. 077 ●●●●●●
★★★★★Soft_wsx★★★★★
*/
-- Double numbers for tree structure processing (extended deep sorting)
If objectproperty (object_id ('tb'), 'isusertable') <> 0
Drop table TB
Create Table Tb (ybh nvarchar (10), ebh nvarchar (10), beizhu nvarchar (1000 ))
Insert TB
Select '123', null, 'yunnan province'
Union all select '20180101', '20180101', 'kunming City'
Union all select '20180101', '20180101', 'zhaotong'
Union all select '20180101', '20180101', 'dali City'
Union all select '20180101', null, 'sichuan province'
Union all select '20180101', null, 'guizhou province'
Union all select '000000', '000000', 'wuhua district'
Union all select '20180101', '20180101', 'shuifu County'
Union all select '000000', '000000', 'no. 0006, Westpark Road'
Union all select '000000', '000000', 'golden Wutong'
Union all select '000000', '000000', 'Technology Co., Ltd'
Union all select '20180101', '20180101', 'two bowls of Towns'
Union all select '20180101', '20180101', 'two bowls cune'
Union all select '000000', '000000', 'chairman of a multinational group'
Union all select '20180101', '20180101', 'chengdu City'

-- Select * from TB
-- Sort by breadth (first display the first layer of nodes and then display the second node ......)
-- Define a secondary table
Declare @ level_tb table (BH nvarchar (10), Level INT)
Declare @ level int
Set @ level = 0
Insert @ level_tb (BH, level)
Select ybh, @ level from TB where ebh is null
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert @ level_tb (BH, level)
Select ybh, @ level
From tb a, @ level_tb B
Where a. ebh = B. bh
And B. Level = @ level-1
End
Select a. *, B. * from tb a, @ level_tb B where a. ybh = B. bh order by level
/*
Ybh ebh beizhu BH level
0001 null Yunnan 0001 0
0008 null Sichuan 0008 0
0004 null Guizhou 0004 0
0002 0001 Kunming 0002 1
0003 0001 Zhaotong City 0003 1
0009 0001 Dali City 0009 1
0014 0008 Chengdu 0014 1
0005 0002 Wuhua district 0005 2
0007 0002 shuifu County 0007 2
0006 0005 West Park Road 192 0006 3
0015 0007 two bowls township 0015 3
0010 0006 golden Wutong 0010 4
0013 0015 two bowl village 0013 4
0011 0010 Technology Co., Ltd. 0011 5
0012 0013 Chairman of a multinational group 0012 5
*/

-- Deep sorting (Simulating Single encoding)
Declare @ level_tt table (ybh nvarchar (1000), ebh nvarchar (1000), Level INT)
Declare @ level int
Set @ level = 0
Insert @ level_tt (ybh, ebh, level)
Select ybh, ybh, @ level from TB where ebh is null
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert @ level_tt (ybh, ebh, level)
Select a. ybh, B. ebh + A. ybh, @ level
From tb a, @ level_tt B
Where a. ebh = B. ybh and B. Level = @ level-1
End
Select space (B. level * 2) + '----' + A. beizhu, A. *, B .*
From tb a, @ level_tt B
Where a. ybh = B. ybh
Order by B. ebh
/* (No column name) ybh ebh beizhu ybh ebh level
---- Yunnan Province 0001 null Yunnan Province 0001 0001 0
---- Kunming 0002 0001 Kunming 0002 00010002 1
---- Wuhua district 0005 0002 Wuhua district 0005 000100020005 2
---- 192, No. 0006, Xiyuan Road, No. 0005, 192, 0006, 0001000200050006, 3
---- Golden Wutong 0010 0006 golden Wutong 0010 00010002000500060010 4
---- Technology Co., Ltd. 0011 0010 Technology Co., Ltd. 0011 000100020005000600100011 5
---- Shuifu County 0007 0002 shuifu County 0007 000100020007 2
---- Two bowls township 0015 0007 two bowls township 0015 0001000200070015 3
---- Two bowls village 0013 0015 two bowls village 0013 00010002000700150013 4
---- Chairman of a multinational group 0012 0013 Chairman of a multinational group 0012 000100020007001500130012 5
---- Zhaotong City 0003 0001 Zhaotong City 0003 00010003 1
---- Dali City 0009 0001 Dali City 0009 00010009 1
---- Guizhou 0004 null Guizhou 0004 0004 0
---- Sichuan 0008 null Sichuan 0008 0008 0
---- Chengdu 0014 0008 Chengdu 0014 00080014 1
*/



-- Search for subnodes (including their own nodes and subnodes)
Declare @ level_tt table (ybh nvarchar (1000), ebh nvarchar (1000), Level INT)
Declare @ level int
Set @ level = 0
Insert @ level_tt (ybh, ebh, level)
Select ybh, ybh, @ level from TB where ybh = '000000'
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert @ level_tt (ybh, ebh, level)
Select a. ybh, B. ebh + A. ybh, @ level
From tb a, @ level_tt B
Where a. ebh = B. ybh and B. Level = @ level-1
End
Select space (B. level * 2) + '----' + A. beizhu, A. *, B .*
From tb a, @ level_tt B
Where a. ybh = B. ybh
Order by B. ebh

/*
(No column name) ybh ebh beizhu ybh ebh level
---- Wuhua district 0005 0002 Wuhua district 0005 0005 0
---- 192, 0006, 0005, 192, 0006, 00050006
---- Golden Wutong 0010 0006 golden Wutong 0010 000500060010 2
---- Technology Co., Ltd. 0011 0010 Technology Co., Ltd. 0011 0005000600100011 3
*/

-- Query the parent node (including its own node and all your nodes)
Declare @ level_tt table (ybh nvarchar (1000), ebh nvarchar (1000), Level INT)
Declare @ level int
Set @ level = 0
Insert @ level_tt (ybh, ebh, level)
Select ybh, ebh, @ level from TB where ebh = '000000'
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert @ level_tt (ybh, ebh, level)
Select a. ebh, B. ebh + A. ebh, @ level
From tb a, @ level_tt B
Where a. ybh = B. ybh and B. Level = @ level-1
End
Select space (B. level * 2) + '----' + A. beizhu, A. *, B .*
From tb a, @ level_tt B
Where a. ybh = B. ybh
Order by B. ebh DESC

/*
(No column name) ybh ebh beizhu ybh ebh level
---- Yunnan Province 0001 null Yunnan Province 0001 0005000500020001 3
---- Kunming 0002 0001 Kunming 0002 000500050002 2
---- Wuhua district 0005 0002 Wuhua district 0005 00050005 1
---- 192, 0006, 0005, 192, 0006, 0005
*/

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/soft_wsx/archive/2009/09/04/4521091.aspx