1. 自身连接查询
无限级分类的实现形式
就是一个类别写有很多个子类,每个子类下面可能还有子类…
比如服装下还有男装,女装,
男装下还有夹克,衬衫等
首先创建一个表cate
mysql> CREATE TABLE cate(
-> id SMALLINT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
-> cateName VARCHAR(100) UNIQUE NOT NULL COMMENT '分类名称',
-> pId SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父id'
-> );
Query OK, 0 rows affected (0.54 sec)
mysql> DESC cate;
+----------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| cateName | varchar(100) | NO | UNI | NULL | |
| pId | smallint unsigned | NO | | 0 | |
+----------+-------------------+------+-----+---------+----------------+
3 rows in set (0.07 sec)
–插入父类
mysql> INSERT cate(cateName, pId) VALUES('服装', 0),
-> ('数码', 0),
-> ('玩具', 0);
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
–插入服装的子分类
mysql> INSERT cate(cateName, pId) VALUES('男装', 1),
-> ('女装', 1),
-> ('内衣', 1);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
–插入数码类的子分类
mysql> INSERT cate(cateName, pId) VALUES('电视', 2),
-> ('冰箱', 2),
-> ('洗衣机', 2);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
–插入玩具类子类
mysql> INSERT cate(cateName, pId) VALUES('爱马仕', 3),
-> ('LV', 3),
-> ('GUCCI', 3);
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
–插入男装的子分类
mysql> INSERT cate(cateName, pId) VALUES('夹克', 4),
-> ('衬衫', 4),
-> ('裤子', 4);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
–插入电视的子分类、
mysql> INSERT cate(cateName, pId) VALUES('液晶电视', 7),
-> ('等离子电视', 7),
-> ('北投电视', 7);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
–查询所有的分类信息,并且得到其父分类
mysql> SELECT s.id, s.cateName AS sCate, p.cateName AS pCate
-> FROM cate AS s
-> LEFT JOIN cate AS p
-> ON s.pId = p.id;
+----+-----------------+--------+
| id | sCate | pCate |
+----+-----------------+--------+
| 1 | 服装 | NULL |
| 2 | 数码 | NULL |
| 3 | 箱包 | NULL |
| 4 | 男装 | 服装 |
| 5 | 女装 | 服装 |
| 6 | 内衣 | 服装 |
| 7 | 电视 | 数码 |
| 8 | 冰箱 | 数码 |
| 9 | 洗衣机 | 数码 |
| 10 | 爱马仕 | 箱包 |
| 11 | LV | 箱包 |
| 12 | GUCCI | 箱包 |
| 13 | 夹克 | 男装 |
| 14 | 衬衫 | 男装 |
| 15 | 裤子 | 男装 |
| 16 | 液晶电视 | 电视 |
| 17 | 等离子电视 | 电视 |
| 18 | 北投电视 | 电视 |
+----+-----------------+--------+
18 rows in set (0.00 sec)
–查询子所有分类及其子分类
mysql> SELECT p.id, p.cateName AS pCate, s.cateName AS sCate
-> FROM cate AS s
-> RIGHT JOIN cate AS p
-> ON s.pId = p.id;
+----+-----------------+-----------------+
| id | pCate | sCate |
+----+-----------------+-----------------+
| 12 | GUCCI | NULL |
| 11 | LV | NULL |
| 6 | 内衣 | NULL |
| 8 | 冰箱 | NULL |
| 18 | 北投电视 | NULL |
| 13 | 夹克 | NULL |
| 5 | 女装 | NULL |
| 2 | 数码 | 电视 |
| 2 | 数码 | 冰箱 |
| 2 | 数码 | 洗衣机 |
| 1 | 服装 | 男装 |
| 1 | 服装 | 女装 |
| 1 | 服装 | 内衣 |
| 9 | 洗衣机 | NULL |
| 16 | 液晶电视 | NULL |
| 10 | 爱马仕 | NULL |
| 7 | 电视 | 液晶电视 |
| 7 | 电视 | 等离子电视 |
| 7 | 电视 | 北投电视 |
| 4 | 男装 | 夹克 |
| 4 | 男装 | 衬衫 |
| 4 | 男装 | 裤子 |
| 17 | 等离子电视 | NULL |
| 3 | 箱包 | 爱马仕 |
| 3 | 箱包 | LV |
| 3 | 箱包 | GUCCI |
| 14 | 衬衫 | NULL |
| 15 | 裤子 | NULL |
+----+-----------------+-----------------+
28 rows in set (0.00 sec)
–查询所有分类的名字并且得到子分类的数目
mysql> SELECT p.id, p.cateName AS pCate,COUNT(s.cateName) AS count
-> FROM cate AS s
-> RIGHT JOIN cate AS p
-> ON s.pId = p.id
-> GROUP BY p.cateName
-> ORDER BY id ASC;
+----+-----------------+-------+
| id | pCate | count |
+----+-----------------+-------+
| 1 | 服装 | 3 |
| 2 | 数码 | 3 |
| 3 | 箱包 | 3 |
| 4 | 男装 | 3 |
| 5 | 女装 | 0 |
| 6 | 内衣 | 0 |
| 7 | 电视 | 3 |
| 8 | 冰箱 | 0 |
| 9 | 洗衣机 | 0 |
| 10 | 爱马仕 | 0 |
| 11 | LV | 0 |
| 12 | GUCCI | 0 |
| 13 | 夹克 | 0 |
| 14 | 衬衫 | 0 |
| 15 | 裤子 | 0 |
| 16 | 液晶电视 | 0 |
| 17 | 等离子电视 | 0 |
| 18 | 北投电视 | 0 |
+----+-----------------+-------+
18 rows in set (0.00 sec)