ClosureTable

 

 

 

 

 

 

1. 查询所有子节点 SELECT `chidren_id` FROM `xi_category4_closure` WHERE `parent_id` = 0 AND `distance` > 0

2. 查询下级子节点 SELECT `chidren_id` FROM `xi_category4_closure` WHERE `parent_id` = 1 AND `distance` = 1

3. 查询由根节点到x节点之间的所有节点，按照层级由小到大排序 SELECT `parent_id` FROM `xi_category4_closure` WHERE `chidren_id` = 8 ORDER BY `distance` DESC

4. 查询由x节点（含）到y节点（不含）之间的所有节点，按照层级由小到大排序 SELECT `parent_id` FROM `xi_category4_closure` WHERE `chidren_id` = 7 AND `distance` < (SELECT `distance` FROM `xi_category4_closure` WHERE `chidren_id` = 7 AND `parent_id` = 0) ORDER BY `distance` DESC

5. 查询x节点到根节点层级 SELECT `distance` FROM `xi_category4_closure` WHERE `chidren_id` = 5 AND `parent_id` = 0

6. 查询x节点到y节点层级 SELECT `distance` FROM `xi_category4_closure` WHERE `chidren_id` = 5 AND `parent_id` = 1

7. 查询某一层的所有节点 SELECT `chidren_id` FROM `xi_category4_closure` WHERE `distance` = 2 AND `parent_id` = 0

1. 插入 INSERT INTO `xi_category4` (`name`) VALUES('紧身T恤') INSERT INTO `xi_category4_closure`(`parent_id`, `chidren_id`, `distance`) (SELECT `parent_id`, 9, `distance` + 1 FROM `xi_category4_closure` WHERE `chidren_id` = 6) INSERT INTO `xi_category4_closure`(`parent_id`, `chidren_id`, `distance`) VALUES(9,9,0)

2. 移动（删除再插入） DELETE FROM `xi_category4_closure` WHERE `chidren_id` = 5