SQL Server alternative non-recursive unlimited classification (Stored Procedure Edition)

The following are my statistical solutions:

Solution 1 (recursive ):

Simple Table Structure:
Categoryid int (4 ),
Categoryname nvarchar (50 ),
Parentid int (4 ),
Depth int (4)
In this way, the parent directory of parentid is obtained recursively based on the first-level application of parentid.
You can also add categoryleft and categoryright to save the parent directory or sub-directory.

Solution 2:
Set a varchar categorypath field to save the complete directory path and separate the parent directory IDs with symbols. For example :,

Solution 3:
Method for increasing the number of double digits for each classification
Example:
Level 1 category: 01,02, 03, 04...
Level 2 Category: 0101,0102, 0103,010 4...
Level 3 classification: 010101,010102, 010103...

After analysis, in fact, the third solution cannot be infinitely classified. However, in the second solution, although it is easy to obtain classification information of superiors and lower levels. However, adding and transferring a category can be difficult.
Furthermore, it completely violates the database design paradigm.

In fact, I have been using the second solution. For ease of searching, I sometimes add categoryid and categorypath to the news table.

What I want to talk about todayAlgorithmIn fact, it is the ultimate version of the second solution. Generally, classification uses a table to store classification information.
Here, I want to create two new tables. One is to save the classification information table and the other is to save the classification relationship table.

The table structure is as follows:
Table 1: tomi_category
Categoryid int (4), 'No.
Categoryname nvarchar (50), 'category name
Depth int (4), 'depth
Table 2: tomi_categorybind
Categoryid int (4 ),
Bindcategoryid int (4 ),
Depth int (4 ),

Adding, editing, and deleting operations are troublesome .. I directly use stored procedures .. I don't know if you can understand it .. Haha.
1. Add a category (category_add) CopyCodeThe Code is as follows: Create proc [DBO]. [category_add]
@ Categoryname nvarchar (50 ),
@ Bindcategoryid int,
@ Categoryid int output
As
Declare @ success bit
Set @ success = 1

-- Generate non-repeated categoryid
Declare @ I bit
Set @ I = 0
While @ I = 0
Begin
Set @ categoryid = left (10000000 + convert (bigint, ABS (checksum (newid (), 8)
If (not exists (select categoryid from tomi_category where categoryid = @ categoryid ))
Set @ I = 1
End

-- Get depth
Declare @ depth int
Set @ depth = 0
Select @ depth = depth from tomi_category where categoryid = @ bindcategoryid
Set @ depth = @ depth + 1

-- Insert
Begin tran
Insert into tomi_category (categoryid, categoryname, depth) values (@ categoryid, @ categoryname, @ depth)
If (@ error <> 0)
Begin
Rollback tran
Set @ success = 0
End

Insert into tomi_categorybind (categoryid, bindcategoryid, depth) values (@ categoryid, @ categoryid, @ depth)
If (@ error <> 0)
Begin
Rollback tran
Set @ success = 0
End

Insert into tomi_categorybind (categoryid, bindcategoryid, depth) Select @ categoryid, bindcategoryid, depth from tomi_categorybind where categoryid = @ bindcategoryid
If (@ error <> 0)
Begin
Rollback tran
Set @ success = 0
End
Commit tran

Print @ categoryid

Each category has a complete directory structure in tomi_categorybind .. The number of records of a category in tomi_categorybind is equal to the value of depth in tomi_category.

Image:

2. Edit and modify a category (category_edit)
Copy code The Code is as follows: Create proc [DBO]. [category_edit]
@ Categoryid int,
@ Categoryname nvarchar (50 ),
@ Bindcategoryid int
As
-- Update
Begin tran
Update tomi_category set categoryname = @ categoryname where categoryid = @ categoryid
If @ error <> 0
Begin
Rollback tran
Return 0
End
Commit tran
-- Check whether the parent directory has been changed
Declare @ is bit
Set @ is = 0
If (exists (select categoryid from tomi_categorybind where categoryid = @ categoryid and bindcategoryid = @ bindcategoryid and depth = (select depth-1 from tomi_category where categoryid = @ categoryid )))
Set @ is = 1
Print @ is
-- Changed the depth
If (@ is = 0)
Begin
-- Obtain the depth of the parent directory.
Declare @ depth int
Set @ depth = 0
Select @ depth = depth from tomi_category where categoryid = @ bindcategoryid
Set @ depth = @ depth + 1
-- Print @ depth
-- Change the subdirectory
Declare @ I int
Declare @ scategoryid int
Declare @ sbindcategoryid int
Declare @ tcategoryidlist table
(
Categoryid int,
Flagid tinyint
)
Insert @ tcategoryidlist select C. categoryid, 0 from tomi_category C left join tomi_categorybind B on C. categoryid = B. categoryid where B. bindcategoryid = @ categoryid order by C. Depth
Set @ I = 1
Set @ sbindcategoryid = @ bindcategoryid
Declare @ errs int
Set @ errs = 0
Begin tran
While (@ I> = 1)
Begin
Select @ scategoryid = 0
Select top 1 @ scategoryid = categoryid from @ tcategoryidlist where flagid = 0
Set @ I =rowcount
-- Print @ scategoryid
If @ scategoryid> 0
Begin
-- Delete and update
Delete from tomi_categorybind where categoryid = @ scategoryid
Set @ errs = @ errs + @ Error
Update tomi_category set depth = @ depth where categoryid = @ scategoryid
Set @ errs = @ errs + @ Error
-- Insert
Insert into tomi_categorybind (categoryid, bindcategoryid, depth) values (@ scategoryid, @ scategoryid, @ depth)
Set @ errs = @ errs + @ Error
Insert into tomi_categorybind (categoryid, bindcategoryid, depth) Select @ scategoryid, bindcategoryid, depth from tomi_categorybind where categoryid = @ sbindcategoryid
Set @ errs = @ errs + @ Error
Set @ sbindcategoryid = @ scategoryid
Set @ depth = @ depth + 1
-- Print @ scategoryid
-- Print @ sbindcategoryid
-- Print @ depth
-- Print '--'
End
Update @ tcategoryidlist set flagid = 1 where categoryid = @ scategoryid
End
If (@ errs> 0)
Begin
Rollback tran
Return 0
End
Else
Commit tran
End

3. Deleting a category (category_del) directly deletes a subcategory.
Copy codeThe Code is as follows: Create proc category_del
@ Categoryid int
As
Begin tran
Delete from tomi_category where categoryid in (select categoryid from tomi_categorybind where categoryid = @ categoryid or bindcategoryid = @ categoryid)
If (@ error <> 0)
Begin
Rollback tran
Return 0
End
Delete from tomi_categorybind where categoryid in (select categoryid from tomi_categorybind where categoryid = @ categoryid or bindcategoryid = @ categoryid)
If (@ error <> 0)
Begin
Rollback tran
Return 0
End
Commit tran

4. Category list, displaying category (category_list)

Copy code The Code is as follows: Create proc category_list
As
Select C. * From tomi_category C left join tomi_categorybind B on C. categoryid = B. categoryid where B. Depth = 1 order by B. bindcategoryid, C. Depth

Go

Exec category_list can be directly used to query classification levels. If all data is displayed, you only need to judge the depth value for one query.
Image:

5. List of parent subcategories (category_uptree) Copy codeThe Code is as follows: Create proc category_uptree
@ Categoryid int
As
Select C. * From tomi_category C left join tomi_categorybind B on C. categoryid = B. bindcategoryid where B. categoryid = @ categoryid order by C. Depth
Go

Exec category_uptree 63919523 so that you can obtain a complete sub-directory set of the classification. For convenience, you only need an SQL statement.
Image:

6. subcategory list (category_downtree)
Copy codeThe Code is as follows: Create proc category_downtree
@ Categoryid int
As
Select C. * From tomi_category C left join tomi_categorybind B on C. categoryid = B. categoryid where B. bindcategoryid = @ categoryid order by C. Depth
Go

Exec category_downtree 21779652 to obtain a complete sub-directory of the classification. For example, you can use this to obtain all products under a category and its subcategories .. Convenience: one SQL statement.
Image:

The above is the first draft, just randomly tested several times... If there is any error, please note it ..

Haha. For more information, see the link. Blog is the first in the blog community. Thank you.
Author: tomiwong
Time: 2010.07.18