自身连接查询

自身连接查询

无限级分类的实现形式

测试

这里的父id是什么东西呢？

例如，在一个电商网站上会有侧边的导航栏，导航栏里面可能会有很多次级栏。

比如一个服装栏，它可以作为顶级分类，它的父id就是0；

长袖和裙子隶属于服装，父id为1；

8分袖隶属于长袖，父id为2

-- 创建数据库分类表
CREATE TABLE cate1(
	id SMALLINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号',
    cateName VARCHAR(100) NOT NULL UNIQUE COMMENT '分类名称',
    pId SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父id'
);

-- 插入顶级分类
INSERT cate1(cateName,pId) VALUES('服装',0),
('数码',0),
('玩具',0);

-- 插入服装的子分类
INSERT cate1(cateName,pId) VALUES('男装',1),
('女装',1),
('内衣',1);

-- 插入数码的子分类
INSERT cate1(cateName,pId) VALUES('电视',2),
('冰箱',2),
('洗衣机',2);

-- 插入玩具的子分类
INSERT cate1(cateName,pId) VALUES('爱马仕',3),
('LV',3),
('GUCCI',3);

-- 插入男装的子分类
INSERT cate1(cateName,pId) VALUES('夹克',4),
('衬衫',4),
('裤子',4);

-- 插入电视的子分类
INSERT cate1(cateName,pId) VALUES('液晶电视',10),
('等离子电视',10),
('背投电视',10);

在插入到内衣记录时，使用语句改变AUTO_INCREMENT的值

ALTER TABLE cate1 AUTO_INCREMENT=10;

+----+------------+-----+
| id | cateName   | pId |
+----+------------+-----+
|  1 | 服装       |   0 |
|  2 | 数码       |   0 |
|  3 | 玩具       |   0 |
|  4 | 男装       |   1 |
|  5 | 女装       |   1 |
|  6 | 内衣       |   1 |
| 10 | 电视       |   2 |
| 11 | 冰箱       |   2 |
| 12 | 洗衣机     |   2 |
| 13 | 爱马仕     |   3 |
| 14 | LV         |   3 |
| 15 | GUCCI      |   3 |
| 16 | 夹克       |   4 |
| 17 | 衬衫       |   4 |
| 18 | 裤子       |   4 |
| 19 | 液晶电视   |  10 |
| 20 | 等离子电视 |  10 |
| 21 | 背投电视   |  10 |
+----+------------+-----+

查询所有分类信息 并且得到其父类

-- 查询所有分类信息 并且得到其父类
SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate1 AS s
LEFT JOIN cate1 AS p
ON s.pId=p.id;

+----+------------+-----------+
| id | sCateName  | pCateName |
+----+------------+-----------+
|  1 | 服装       | NULL      |
|  2 | 数码       | NULL      |
|  3 | 玩具       | NULL      |
|  4 | 男装       | 服装      |
|  5 | 女装       | 服装      |
|  6 | 内衣       | 服装      |
| 10 | 电视       | 数码      |
| 11 | 冰箱       | 数码      |
| 12 | 洗衣机     | 数码      |
| 13 | 爱马仕     | 玩具      |
| 14 | LV         | 玩具      |
| 15 | GUCCI      | 玩具      |
| 16 | 夹克       | 男装      |
| 17 | 衬衫       | 男装      |
| 18 | 裤子       | 男装      |
| 19 | 液晶电视   | 电视      |
| 20 | 等离子电视 | 电视      |
| 21 | 背投电视   | 电视      |
+----+------------+-----------+

查询所有的分类及其子分类

-- 查询所有的分类及其子分类
SELECT p.id,p.cateName AS pCateName,s.id,s.cateName AS sCateName
FROM cate1 AS s
RIGHT JOIN cate1 AS p
ON s.pId=p.id;

+----+------------+------+------------+
| id | pCateName  | id   | sCateName  |
+----+------------+------+------------+
|  1 | 服装       |    4 | 男装       |
|  1 | 服装       |    5 | 女装       |
|  1 | 服装       |    6 | 内衣       |
|  2 | 数码       |   10 | 电视       |
|  2 | 数码       |   11 | 冰箱       |
|  2 | 数码       |   12 | 洗衣机     |
|  3 | 玩具       |   13 | 爱马仕     |
|  3 | 玩具       |   14 | LV         |
|  3 | 玩具       |   15 | GUCCI      |
|  4 | 男装       |   16 | 夹克       |
|  4 | 男装       |   17 | 衬衫       |
|  4 | 男装       |   18 | 裤子       |
| 10 | 电视       |   19 | 液晶电视   |
| 10 | 电视       |   20 | 等离子电视 |
| 10 | 电视       |   21 | 背投电视   |
| 15 | GUCCI      | NULL | NULL       |
| 14 | LV         | NULL | NULL       |
|  6 | 内衣       | NULL | NULL       |
| 11 | 冰箱       | NULL | NULL       |
| 16 | 夹克       | NULL | NULL       |
|  5 | 女装       | NULL | NULL       |
| 12 | 洗衣机     | NULL | NULL       |
| 19 | 液晶电视   | NULL | NULL       |
| 13 | 爱马仕     | NULL | NULL       |
| 20 | 等离子电视 | NULL | NULL       |
| 21 | 背投电视   | NULL | NULL       |
| 17 | 衬衫       | NULL | NULL       |
| 18 | 裤子       | NULL | NULL       |
+----+------------+------+------------+

查询所有的分类并且得到其子分类的数目

-- 查询所有的分类并且得到其子分类的数目
SELECT p.id,p.cateName AS pCateName,GROUP_CONCAT(s.cateName) AS sCateName,COUNT(s.cateName) AS sCount
FROM cate1 AS p
LEFT JOIN cate1 AS s
ON p.id=s.pId
GROUP BY p.id;

+----+------------+------------------------------+--------+
| id | pCateName  | sCateName                    | sCount |
+----+------------+------------------------------+--------+
|  1 | 服装       | 男装,女装,内衣               |      3 |
|  2 | 数码       | 冰箱,洗衣机,电视             |      3 |
|  3 | 玩具       | 爱马仕,LV,GUCCI              |      3 |
|  4 | 男装       | 夹克,衬衫,裤子               |      3 |
|  5 | 女装       | NULL                         |      0 |
|  6 | 内衣       | NULL                         |      0 |
| 10 | 电视       | 液晶电视,等离子电视,背投电视 |      3 |
| 11 | 冰箱       | NULL                         |      0 |
| 12 | 洗衣机     | NULL                         |      0 |
| 13 | 爱马仕     | NULL                         |      0 |
| 14 | LV         | NULL                         |      0 |
| 15 | GUCCI      | NULL                         |      0 |
| 16 | 夹克       | NULL                         |      0 |
| 17 | 衬衫       | NULL                         |      0 |
| 18 | 裤子       | NULL                         |      0 |
| 19 | 液晶电视   | NULL                         |      0 |
| 20 | 等离子电视 | NULL                         |      0 |
| 21 | 背投电视   | NULL                         |      0 |
+----+------------+------------------------------+--------+