模拟3级分类信息查询

模拟3级分类信息查询

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `sort` int(11) NOT NULL,
  `parentId` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `course` VALUES ('1', 'java', '1', '0');
INSERT INTO `course` VALUES ('1-1', 'javase', '1', '1');
INSERT INTO `course` VALUES ('1-1-1', 'servlet', '1', '1-1');
INSERT INTO `course` VALUES ('1-1-2', 'filter', '2', '1-1');
INSERT INTO `course` VALUES ('1-2', 'javaweb', '2', '1');
INSERT INTO `course` VALUES ('1-3', 'javassm', '3', '1');
INSERT INTO `course` VALUES ('2', 'python', '2', '0');
INSERT INTO `course` VALUES ('3', 'go', '3', '0');

SELECT
	A.id as one_id, A.name as one_name,
	B.id as two_id, B.name as two_name,
	C.id as three_id, C.name as three_name
FROM
	course A
	LEFT JOIN course B
	on B.parentId = A.id
	LEFT JOIN course C
	on C.parentId = B.id
where 
	A.parentId = 0
order BY
	A.sort, B.sort, C.sort