mysql之自身连接查询

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)