http://q.cnblogs.com/q/39658/
On the design of the table structure of the infinite Classification database, traversing the efficiency of all products under a certain classification
The database table structure is like this:
[Category] Category table
ID (int) Category ID
ParentID (int) parent class ID
name (nvarchar (50)) category names
[Product] Product table
Id (int) Product name
name (nvarchar (50)) Product names
CategoryID (int) Category ID (corresponds to [Category]. ID)
Info (ntext) Introduction information
......
[Category] Category table data
Id ParentID Name
1 0 Garments
2 1 Men's Clothing
3 2 Shirts
4 2 Trousers
5 2 Shorts
6 2 T-shirts
7 2 Coats
8 2 Sweater
9 2 Suits
10 4 Casual Pants
11 4 Trousers
12 4 Jeans
13 4 Sports Pants
[Product] Products table data no longer one by one examples
Id Name CategoryID Info
1
2
3
......
======================================================= traversal is as follows
Id ParentID Name Path
1 0 Garments, 1,
2 1 men, 1, 2,
3 2 shirts, 1,2,3,
4 2 trousers, 1,2,4,
5 2 shorts, 1,2,5,
.....
SELECT * FROM [Product] p where P.categoryid in (select-Id from [Category] C where c.path like (select Path from [Catego RY] where id=2) + '% '
A table:
Trademark Category trademark Category name parent class
1 trademark Big Class 0
2 Trademark Two this Class 1 1
3 Trademark Two This class 2 1
4 Trademark Two This class 3 1
5 Trademark Two This class 4 12 level menu
Select a.ID Commodity category, A.title, b.id level two number from wp_shopcategory A,wp_shopcategory b where a.id=b.pid
Level three Menu
Select a.ID Commodity category, A.title, b.id level two number, c.id three level number, C.title three class name from Wp_shopcategory a,wp_shopcategory B, wp_shopcategory c where a.id=b.pid and b.id = C.pid
http://blog.csdn.net/acmain_chm/article/details/4142971