Processing tree data

/* -- Table structure description and data environment:

Table Name TB. If you modify the table name, modify the table name TB involved in all data processing.
ID is the ID (Id field + primary key), PID is the upper-level ID, and name is the name. Other fields can be added later.
Unless otherwise specified, the added field will not affect the processing result.

-- Producer build 2003.12 (reference please keep this information )--*/

-- Test Data
Create Table Tb (
Id int identity (1, 1) not null constraint pk_tb primary key clustered,
PID int, name varchar (20 ))
Insert TB select 0, 'China'
Union all select 0, 'America'
Union all select 0, 'Canada'
Union all select 1, 'beijing'
Union all select 1, 'shanghai'
Union all select 1, 'jiangsu'
Union all select 6, 'suzhou'
Union all select 7, 'changshu'
Union all select 6, 'nanjing'
Union all select 6, 'wuxi'
Union all select 2, 'newyork'
Union all select 2, 'san Francisco'
Go

-- 1. User-Defined Function -- get the total number of codes
Create Function f_getmergid (@ id int)
Returns varchar (8000)
As
Begin
Declare @ Re varchar (8000), @ PID int

-- Uniform coding width is required for normal numeric sorting
Declare @ idlen int, @ idheader varchar (20)
Select @ idlen = max (LEN (ID ))
, @ Idheader = space (@ idlen)
From TB

-- Get the total number of codes
Set @ Re = right (@ idheader + Cast (@ ID as varchar), @ idlen)
Select @ pid = PID from TB where id = @ ID
While @ rowcount> 0
Select @ Re = right (@ idheader + Cast (@ PID as varchar), @ idlen) + ',' + @ Re
, @ Pid = PID from TB where id = @ PID
Return (@ Re)
End
Go

-- 2. Custom function -- checks whether a code is referenced cyclically.
Create Function f_chkid (@ id int)
Returns bit -- loop, 1 is returned; otherwise, 0 is returned.
As
Begin
Declare @ Re bit, @ PID int
 
Set @ Re = 0

-- Detection
Select @ pid = PID from TB where id = @ ID
While @ rowcount> 0
Begin
If @ pid = @ ID
Begin
Set @ Re = 1
Goto lberr
End
Select @ pid = PID from TB where id = @ PID
End

Lberr:
Return (@ Re)
End
Go

/* -- Data Replication

If the table contains custom fields, you need to modify the Stored Procedure
The nested layer cannot exceed 32 layers.
--*/

-- 3. Copy the child node under the specified node to another node
Create proc p_copy
@ S_id int, -- copy all sub-items under the item
@ D_id int, -- copy to this item
@ New_id int -- start number of the add item
As
Declare @ NID int, @ OID int, @ name varchar (20)
Select ID, name into # temp from TB where pid = @ s_id and ID <@ new_id
While exists (select 1 from # temp)
Begin
Select @ OID = ID, @ name = Name from # temp
Insert into TB values (@ d_id, @ name)
Set @ nid = @ identity
Exec p_copy @ OID, @ NID, @ new_id
Delete from # temp where id = @ OID
End
Go

-- 4. batchcopy stored procedure-copy a specified node and all its subnodes and generate a new node
Create proc p_copystr
@ S_id varchar (8000) -- List of items to be replicated, separated by commas
As
Declare @ NID int, @ OID int, @ name varchar (20)
Set @ s_id = ',' + @ s_id + ','
Select ID, name into # temp from TB
Where charindex (',' + Cast (ID as varchar) + ',', @ s_id)> 0
While exists (select 1 from # temp)
Begin
Select @ OID = ID, @ name = Name from # temp
Insert into TB values (@ OID, @ name)
Set @ nid = @ identity
Exec p_copy @ OID, @ NID, @ Nid
Delete from # temp where id = @ OID
End
Go

-- 5. Obtain the subid list of the specified ID.
Create Function f_getchildid (@ id int)
Returns @ Re table (id int)
As
Begin
Insert into @ Re select ID from TB where pid = @ ID
While @ rowcount> 0
Insert into @ Re select a. ID
From TB a inner join @ Re B on A. PID = B. ID
Where a. id not in (select ID from @ Re)
Return
End
Go

-- 6. Obtain the parent ID list of the specified ID.
Create Function f_getparentid (@ id int)
Returns @ Re table (id int)
As
Begin
Declare @ PID int
Select @ pid = PID from TB where id = @ ID
While @ PID <> 0
Begin
Insert into @ Re values (@ PID)
Select @ pid = PID from TB where id = @ PID
End
Return
End
Go

-- 7. delete a specified Node

Create proc p_delete
@ ID int, -- ID to be deleted
@ Deletechild bit = 0 -- whether to delete sub-1. Delete sub-, 0. If @ ID has sub-, the deletion fails.
As
If @ deletechild = 1
Delete from TB where DBO. f_getmergid (ID) Like DBO. f_getmergid (@ ID) + '%'
Else
If exists (select 1 from TB where pid = @ ID)
Goto lberr
Else
Delete from TB where id = @ ID
Return

Lberr:
Raiserror ('this node has subnodes and cannot be deleted ', 16, 1)
Go

-- 8. Get the total number of codes and the encoding level table. This is for the entire table and should be processed in the full table:

Create Function f_getbmmerg ()

Returns @ Re table (ID int, idmerg varchar (8000), Level INT)

As

Begin
Declare @ idlen int, @ idheader varchar (20), @ level int
Select @ idlen = max (LEN (ID), @ idheader = space (@ idlen) from TB
Set @ level = 1
Insert into @ Re select ID, right (@ idheader + Cast (ID as varchar), @ idlen), @ level
From TB where pid = 0
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert into @ Re select B. ID, A. idmerg + ',' + right (@ idheader + Cast (B. ID as varchar), @ idlen), @ level
From @ re a inner join tb B on A. ID = B. PID
Where a. Level = @ level-1
End
Return
End
Go

-- Application:

/* -- Sort data display --*/
-- Hierarchical display -- horizontal, first level, then Level 2...
Select * from TB order by PID

-- Hierarchical display -- vertical
Select * from TB order by DBO. f_getmergid (ID)
Go

/* -- Data statistics --*/
-- Hierarchical statistics: number of detailed regions in each region
Select *,
Number of detailed regions = (select count (*) from TB where DBO. f_getmergid (ID) Like DBO. f_getmergid (A. ID) + ', % ')
From TB a order by DBO. f_getmergid (ID)

Go
/* -- Add and modify data
 
There is no skill in adding and modifying data (including modifying the category)
You only need to check whether the upper-level has exists. This can be simply solved using the following statement:
If exists (select 1 from TB where id = @ ID) print 'else print' does not exist'
--*/

-- Delete data from 'America'
-- Exec p_delete 2 -- does not contain sub-accounts. deletion may fail because there are sub-accounts in the United States.
Exec p_delete 2, 1 -- contains sub-records. All data in the United States and the United States will be deleted.
Go

For more information, see my post on csdn.

Http://expert.csdn.net/Expert/topic/2285/2285830.xml? Temp =. 1212885.