关于树形数据结构,最好的实现方式是前序遍历,前序遍历既能实现查找整个节点,也能实现查找某一层,或查找父节点,速度快。
组织机构树采用该遍历比较好。
关于这方面的文章参考http://ftp.ntu.edu.tw/ftp/MySQL/tech-resources/articles/hierarchical-data.html
以下借用连接中的图和表为例
1.查找叶子节点,只需判断lft+1与rgt相不相等即可,因为只要该节点下还有节点,这两个字段值就不可能相等。
SELECT nameFROM nested_categoryWHERE rgt = lft + 1;
2.查找一条路径
SELECT node.name, (COUNT(parent.name) - 1) AS depthFROM nested_category AS node,nested_category AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtGROUP BY node.nameORDER BY node.lft;
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+
欢迎加我的qq技术群425783133