ame,parent_id) VALUES('Accessory',2);
INSERT goods_types(type_name,parent_id) VALUES('Laptop',9);
INSERT goods_types(type_name,parent_id) VALUES('Ultrabook',9);
INSERT goods_types(type_name,parent_id) VALUES('Gamebook',9);
INSERT goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT goods_types(type_name,parent_id) VALUES('Host Computer',10);
mysql> SELECT * FROM GOODS_TYPES;
+---------+------------------+-----------+
| type_id | type_name | parent_id |
+---------+------------------+-----------+
| 1 | Home Electronics | 0 |
| 2 | PC?Office | 0 |
| 3 | Appliances | 1 |
| 4 | Life Appliances | 1 |
| 5 | Tablet TV | 3 |
| 6 | Air Conditioner | 3 |
| 7 | Electrical Fan | 4 |
| 8 | Water Dispenser | 4 |
| 9 | Complete Machine | 2 |
| 10 | Accessory | 2 |
| 11 | Laptop | 9 |
| 12 | Ultrabook | 9 |
| 13 | Gamebook | 9 |
| 14 | CPU | 10 |
| 15 | Host Computer | 10 |
+---------+------------------+-----------+
注意parent_id,是指改节点的级别,比如0,表示home electronics没有父节点,是个顶级节点
查找的实现,是通过自身连接来完成的
---自身连接
举个例子,显示所有类别的父类是什么
这里,自身的连接,可以想象右边有一张一模一样的表(左边也可以,有些东西稍微变一下就行)
mysql> SELECT S.TYPE_ID,S.TYPE_NAME,P.TYPE_NAME FROM GOODS_TYPES AS S LEFT JOIN GOODS_TYPES AS P
-> ON S.PARENT_ID=P.TYPE_ID;
+---------+------------------+------------------+
| TYPE_ID | TYPE_NAME | TYPE_NAME |
+---------+------------------+------------------+
| 1 | Home Electronics | NULL |
| 2 | PC?Office | NULL |
| 3 | Appliances | Home Electronics |
| 4 | Life Appliances | Home Electronics |
| 5 | Tablet TV | Appliances |
| 6 | Air Conditioner | Appliances |
| 7 | Electrical Fan | Life Appliances |
| 8 | Water Dispenser | Life Appliances |
| 9 | Complete Machine | PC?Office |
| 10 | Accessory | PC?Office |
| 11 | Laptop | Complete Machine |
| 12 | Ultrabook | Complete Machine |
| 13 | Gamebook | Complete Machine |
| 14 | CPU | Accessory |
| 15 | Host Computer | Accessory |
+---------+------------------+------------------+
翻译一下,就是,可以先从条件说起,当子表s中的parent_id=父表中的type_id时,选取子表的type_id,type_name,以及父表的type_name显示,这里的别名可以在语句的末尾处显示
要把这个逻辑关系先说说/想想清楚,再翻译为sql语句
反过来,查找所有类别的子类
先考虑逻辑关系:当父表的type_id=子表的parent_id时,取 P.TYPE_ID,P.TYPE_NAME,S.TYPE_NAME ——再写出语句
mysql> SELECT P.TYPE_ID,P.TYPE_NAME,S.TYPE_NAME FROM GOODS_TYPES AS P LEFT JOIN GOODS_TYPES AS S
-> ON P.TYPE_ID=S.PARENT_ID;
+---------+------------------+------------------+
| TYPE_ID | TYPE_NAME | TYPE_NAME |
+---------+------------------+------------------+
| 1 | Home Electronics | Appliances |
| 1 | Home Electronics | Life Appliances |
| 3 | Appliances | Tablet TV |
| 3 | Appliances | Air Conditioner |
| 4 | Life Appliances | Electrical Fan |
| 4 | Life Appliances | Water Dispenser |
| 2 | PC?Office | Complete Machine |
| 2 | PC?Office | Accessory |
| 9 | Complete Machine | Laptop |
| 9 | Complete Machine | Ultrabook |
| 9 | Complete Machine | Gamebook |
| 10 | Accessory | CPU |
| 10 | Accessory | Host Computer |
| 5 | Tablet TV | NULL |
| 6 | Air Conditioner | NULL |
| 7 | Electrical Fan | NULL |
| 8 | Water Dispenser | NULL |
| 11 | Laptop | NULL |
| 12 | Ultrabook | NULL |
| 13 | Gamebook | NULL |
| 14 | CPU | NULL |
| 15 | Host Computer | NULL |
+---------+------------------+------------------+
再复杂一点,且留给你们自己翻译
mysql> SELECT P.TYPE_ID,P.TYPE_NAME,COUNT(S.TYPE_NAME) CHILD_COUNT FROM GOODS_TYPES AS P LEFT JOIN GOODS_TYPES AS S
-> ON P.TYPE_ID=S.PARENT_ID GROUP BY P.TYPE_NAME ORDER BY P.TYPE_ID;
+---------+------------------+-------------+
| TYPE_ID | TYPE_NAME | CHILD_COUNT |
+---------+------------------+-------------+
| 1 | Home Electronics | 2 |