创建表
DROP TABLE IF EXISTS `infinite_classification`;
CREATE TABLE `infinite_classification` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`pid` int(11) unsigned DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of infinite_classification
-- ----------------------------
BEGIN;
INSERT INTO `infinite_classification` VALUES (1, '前端', 0);
INSERT INTO `infinite_classification` VALUES (2, '后端', 0);
INSERT INTO `infinite_classification` VALUES (3, '移动', 0);
INSERT INTO `infinite_classification` VALUES (4, 'HTML', 1);
INSERT INTO `infinite_classification` VALUES (5, 'JS', 1);
INSERT INTO `infinite_classification` VALUES (6, '小程序', 1);
INSERT INTO `infinite_classification` VALUES (7, 'JAVA', 2);
INSERT INTO `infinite_classification` VALUES (8, 'PHP', 2);
INSERT INTO `infinite_classification` VALUES (9, 'Go', 2);
INSERT INTO `infinite_classification` VALUES (10, 'Android', 3);
INSERT INTO `infinite_classification` VALUES (11, 'ios', 3);
INSERT INTO `infinite_classification` VALUES (12, 'WEEX', 3);
INSERT INTO `infinite_classification` VALUES (13, 'css', 4);
INSERT INTO `infinite_classification` VALUES (14, 'Sass', 4);
INSERT INTO `infinite_classification` VALUES (15, 'jQuery', 5);
INSERT INTO `infinite_classification` VALUES (16, 'vue', 5);
INSERT INTO `infinite_classification` VALUES (17, 'React', 5);
COMMIT;
查询数据
mysql> select * from infinite_classification
-> ;
+----+-----------+------+
| id | name | pid |
+----+-----------+------+
| 1 | 前端 | 0 |
| 2 | 后端 | 0 |
| 3 | 移动 | 0 |
| 4 | HTML | 1 |
| 5 | JS | 1 |
| 6 | 小程序 | 1 |
| 7 | JAVA | 2 |
| 8 | PHP | 2 |
| 9 | Go | 2 |
| 10 | Android | 3 |
| 11 | ios | 3 |
| 12 | WEEX | 3 |
| 13 | css | 4 |
| 14 | Sass | 4 |
| 15 | jQuery | 5 |
| 16 | vue | 5 |
| 17 | React | 5 |
+----+-----------+------+
17 rows in set (0.00 sec)
联合查询join
mysql> SELECT t1.name AS lev1,t2.name as lev2,t3.name AS lev3 FROM infinite_classification as t1 LEFT JOIN infinite_classification AS t2 ON t2.pid = t1.id LEFT JOIN infinite_classification AS t3 ON t3.pid = t2.id;
+-----------+-----------+--------+
| lev1 | lev2 | lev3 |
+-----------+-----------+--------+
| 前端 | HTML | css |
| 前端 | HTML | Sass |
| 前端 | JS | jQuery |
| 前端 | JS | vue |
| 前端 | JS | React |
| 前端 | 小程序 | NULL |
| 后端 | JAVA | NULL |
| 后端 | PHP | NULL |
| 后端 | Go | NULL |
| 移动 | Android | NULL |
| 移动 | ios | NULL |
| 移动 | WEEX | NULL |
| HTML | css | NULL |
| HTML | Sass | NULL |
| JS | jQuery | NULL |
| JS | vue | NULL |
| JS | React | NULL |
| 小程序 | NULL | NULL |
| JAVA | NULL | NULL |
| PHP | NULL | NULL |
| Go | NULL | NULL |
| Android | NULL | NULL |
| ios | NULL | NULL |
| WEEX | NULL | NULL |
| css | NULL | NULL |
| Sass | NULL | NULL |
| jQuery | NULL | NULL |
| vue | NULL | NULL |
| React | NULL | NULL |
+-----------+-----------+--------+
29 rows in set (0.00 sec)
mysql> SELECT t1.name AS lev1,t2.name as lev2,t3.name AS lev3 FROM infinite_classification as t1 LEFT JOIN infinite_classification AS t2 ON t2.pid = t1.id LEFT JOIN infinite_classification AS t3 ON t3.pid = t2.id where t3.id = 15;
+--------+------+--------+
| lev1 | lev2 | lev3 |
+--------+------+--------+
| 前端 | JS | jQuery |
+--------+------+--------+
1 row in set (0.00 sec)
mysql>
