Description: Discuss how to handle tree data, sort, new, modify, copy, delete, data integrity check, general exchange
Table structure description and data environment:
Table name TB, if the table name is modified, modify the table name TB involved in all data processing
The ID is the number (Identification field primary key), the PID is the superior number, name is name, and it can add other fields after it is available.
Any place that is not specially marked is not impact on the processing result / * - Data test environment table name TB, if the table name is modified, the table name TB ID involved in all data processing is numbered (ID) Field primary key) PID is the name of the superior number Name, and it can add other fields after it is available.
Any place that is not specially marked, does not affect the processing results
- Table environment
Create Table TB (ID Int IDENTITY (1) Not Null Constraint PK_TB Primary Key Clustered, Pid, Name VARCHAR (20)) Insert Into TB SELECT 0, 'China' Union All SELECT 0, 'US' Union All Select 0 , 'Nion 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,' New York 'Union All Slect 2,' San Francisco 'Go
- Functions and stored processes needed in handling
--1. Custom Function - Get Code Cumulative Create Function F_GetMergid (@ID INT) Returns Varchar (8000) Asbegin Declare @re varchar (8000), @ PID INT
- For the normality, you need unified encoding width declare @Idlen int, @ idheader varchar (20) select @ idlen = max (len (id)), @ idheader = space (@Idlen) from TB
- Get encoding accumulated set @ re = right (@id as varchar), @ idlen) select @ PID = PID from TB where id = @ id while @@ Right> 0 Select @ RE = Right (@ IDHEADER CAST (@PID as varchar), @ idlen) ',' @ RE, @ PID = PID from TB where id = @ pid return (@RE) Endgo
--2. Custom Function - Detects a coded departure, whether it is cycled to reference the Create Function F_CHKID (@ID INT) Returns bit - loop, return 1, otherwise return 0asbegin declare @RE bit, @ pid set @re = 0
- Detect 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 overlberr : Return (@RE) Endgo
/ * - Data replication
If the table contains the custom field, there is a need to modify the problem with no more than 32 layers in the stored procedure. - * /
--3. Copy the subjunction point in the specified node to the other node under Create Proc P_copy @ S_ID INT, - Copy all child @d_id int, - copy to this @New_ID INT - New increase number AsDeclare @nid int, @ Oid int, @ name varchar (20) Select ID, Name Into #temp from Tb Where PID = @ S_ID and ID <@new_idwhile exists (SELECT 1 from #temp) Begin select @ OID = ID, @ name = name from #temp insert @ D_ID, @ Name) set @nid = @@ identity exec p_copy @ Oid, @ nid, @ new_id delete from #Temp where id = @ Oidendgo
--4. Batch replication stored procedure - copy the specified node and all sub-nodes below, and generate new nodes create proc p_copystr @ s_id varchar (8000) - To copy the list, separated as Declare with comma @nid int, @ name var, @ Name var, @S_ID = ',' @ S_ID ',' SELECT ID, Name Into #temp from Tbwhere Charindex (',' Cast (ID as varchar) ' , ', @S_ID)> 0WHILE 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 = @ Oidendgo
--6. Sub-ID list of specified ID Create function f_getchildid (@id int) returns @re table (id int) asbegin insert @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) ReturnendGo
--7. Get the parent ID list of the specified ID Create function f_getparentId (@id int) returns @re table (id int) asbegin declare @pid int search @ 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 ReturnendGo - 8. Delete the designated node
Create Proc P_Delete @ id @ deleted ID @ deletechild bit = 0 - Whether to delete the sub-1. Delete sub, 0. If @ID has a child, delete failed .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 (has a sub-node under this node, can't delete ', 16, 1) GO
--9. Get coding cumulative and encoding level table, this is for full table, mainly should be processed in full table:
CREATE FUNCTION F_GETBMMERG ()
Returns @reat Table (ID INT, IDmerg varchar (8000), Level Int
AS
Begin
Declare @Idlen int, @ IDHEADER VARCHAR (20), @LEVEL INT
SELECT @ idlen = max (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:
/ * - Data Show Sort - * / - Hierarchical Display - Landscape, First First, Second Level ... Select * from Tb Order By PID
- Hierarchical display - portrait Select * from Tb Order by dbo.f_getergid (id) Go
/ * - Data Statistics - * / - Hierarchical Statistics, Dianting Areas under Each Region Select *, count number = (Select Count (*) from Tb Where dbo.f_getmerge (ID) Like dbo.F_getMergID A.ID) ',%') from TB A ORDER BY DBO.F_GETMERGID (ID) GO / * - Data New, Modify Data New, Modification (including the category of modification), only need to check Whether the above level is existing. This can be solved simply with the following statement: if EXISTS (SELECT 1 from tb where id = @ id) print 'exists'- * /
- Delete the 'US' 'data --Exec p_delete 2 - not contains the child, because there is a child in the United States, so remove the EXEC P_DELETE 2, 1 - contain the child, will delete the US and all data GO
In the original text, I will see my post on 9CBS.
Http://expert.9cbs.net/expert/topic/2285/2285830.xml?temp=.1212885
