Transferred from: loveyuki-SQL server storage-level data for unlimited Classification

SQL Server storage-level data for unlimited Classification

Because the data stored in the database is stored in a plane, most of the forums and otherProgramIt uses recursion to display hierarchical data. If the classification level is very deep, the number of recursion times is considerable, and the performance is also greatly affected. This problem has been encountered recently when we want to build a platform for classification information. How can we quickly display hierarchical data? MySQL developers help us think of an algorithm.AlgorithmCurrently, the only problem is that classification sorting has not yet been implemented. We can use reverse sorting of the right value to achieve first-in-first-out sorting. Here we need to know how to use SQL Server for implementation. We will use the provincial, municipal, and county databases as an example:

We can see the numbers on the left and right of a tree node.RuleIf the left and right values of Shanxi are (8, 17), all nodes with the left value greater than 8 and the right value less than 17 belong to the sub-nodes of Shanxi. If the left and right values are (), then all of his parent nodes are nodes with a left value less than 14 and a right value greater than 15, the unlimited classification performance achieved by this method is absolutely superb. You can find the data of a node and its sub-nodes in one query. This is an unlimited classification algorithm with the highest performance I have ever seen. Other algorithms have no advantage in comparison with this algorithm.

First, create a data table with a structure such as (lid is the left value, RID is the right value, tree is the node depth, name and ID are not much said, node index and name)

We can use the following stored procedure to obtain a node and Its subnodes:

 
 

  
  
 
  
Create ProcedureClsp_zoneselect
 
  
  
 
  
(
 
  
  

  
@ RootInt,
 
  
  
 
  
@ TreeInt 
 
  
  
 
  
)
 
  
  
 
  
As 
 
  
  
 
  
SelectZ. ID, Z. Tree, Z.Name
 
  
  
 
  
FromCl_zonedataAsZ, cl_zonedataAsP
 
  
  
 
  
WhereP. ID = @ Root
 
  
  

  
AndZ. lid> = P. LidAndZ. Rid <= P. RID
 
  
  
 
  
And(@ Tree = 0OrZ. Tree <= P. Tree + @ tree)
 
  
  
 
  
Order ByZ. LidASC 
 
  
  
 
  
Go
 
 
 

We can use the following stored procedure to insert a new subnode under a node:

 
 

  
  

  
Create ProcedureClsp_zoneinsert
 
  
  
 
  
(
 
  
  
 
  
@ RootInt,
 
  
  
 
  
@NameNvarchar (50)
 
  
  
 
  
)
 
  
  
 
  
As 
 
  
  

  
Declare@ RIDAs Int, @ NidAs Int, @ TreeAs Int 
 
  
  
 
  
 
 
  
  
 
  
Set@ Rid = 1
 
  
  
 
  
Set@ Nid = 0
 
  
  
 
  
Set@ Tree = 1
 
  
  
 
  
 
 
  
  

  
If @ root = 0
 
  
  
 
  
Begin 
 
  
  
 
  
Select Top1 @ rid = rid + 1
 
  
  
 
  
FromCl_catedataOrder ByRIDDesc 
 
  
  
 
  
End 
 
  
  

  
Else 
 
  
  
 
  
Begin 
 
  
  
 
  
Select@ Rid = RID, @ tree = tree + 1
 
  
  
 
  
FromCl_zonedataWhereId = @ Root
 
  
  
 
  
End 
 
  
  
 
  
 
 
  
  

  
If @ root = 0Or@ Rid> 1
 
  
  
 
  
Begin 
 
  
  
 
  
UpdateCl_zonedataSetRid = rid + 2WhereRid> = @ RID
 
  
  
 
  
UpdateCl_zonedataSetLid = lid + 2WhereLid> @ RID
 
  
  
 
  
 
 
  
  

  
Insert IntoCl_zonedata (lid, RID, tree,Name)
 
  
  
 
  
Values(@ RID, @ rid + 1, @ tree ,@Name)
 
  
  
 
  
 
 
  
  
 
  
Set@ Nid = scope_identity ()
 
  
  
 
  
End 
 
  
  
 
  
Select@ Nid
 
  
  

  
Go
 
 
 

You can use the following stored procedure to delete a node:

 
 

  
  
 
  
Create ProcedureClsp_zonedelete
 
  
  
 
  
(
 
  
  
 
  
@ IDInt 
 
  
  
 
  
)
 
  
  
 
  
As 
 
  
  

  
Declare@ LidAs Int, @ RIDAs Int, @ WidAs Int, @ DidAs Int 
 
  
  
 
  
Set@ Did = 0
 
  
  

  
Select@ Did = ID, @ lid = lid, @ rid = RID, @ WID = RID-lid + 1FromCl_zonedataWhereId = @ ID
 
  
  
 
  
If @ did! = 0
 
  
  
 
  
Begin 
 
  
  

  
Delete FromCl_zonedataWhereLidBetween@ LidAnd@ RID
 
  
  
 
  
UpdateCl_zonedataSetRid = RID-@ widWhereRid> @ RID
 
  
  

  
UpdateCl_zonedataSetLid = lid-@ widWhereLid> @ RID
 
  
  
 
  
End 
 
  
  
 
  
Select@ Did
 
  
  
 
  
Go
 
 
 

This Article Edited by loveyuki