MySQL无限级目录的构建与父目录查询

1.问题

当一个数据库中只有一张数据表的时候，是否有可能需要用到连接查询？设学生选课数据库中，使用SQL语句完成查询课程的先修课，列出课程名称以及它的先修课的名称。
当一张表连接自身的连接称为自连接 。其应用场景主要是在构建无限多级目录。比如线上课程的多级目录，商品类别的无限分类，以及本题所探讨的先修课程的问题。
以下就以先修课程为例进行阐述（这里只考虑每门课程只有一个直接先修课程）

2.数据表的创建及数据录入

(1) 创建表专业课程表course

#创建表专业课程表course
create table course
(
	id int primary key auto_increment, #课程id，自增长
	name varchar(20) not null , #课程名
	parent_id int  #先修课程名
);

(2)插入若干课程数据

#插入记录
insert into course values(null,'C语言程序设计',0),
(null,'HTML网页设计基础',0),
(null,'数据库应用基础',1),
(null,'数据结构',1),
(null,'Java程序设计',1),
(null,'Jsp应用程序设计',5),
(null,'SSM框架技术',6),
(null,'数据库高级应用',3),
(null,'软件测试技术',5),
(null,'JavaScript程序设计',2)

(3)查询course数据表

#查询数据表
select * from course;

查询结果如下图所示：

从查询结果可以看出，“C语言程序设计”，“HTML网页设计基础”两门课程没有先修课。其它课程的先修课可以通过parent_id属性找到，比如“数据库应用基础”的先修课程是“C语言程序设计”,而数据库高级应用的先修课是“数据库应用基础”。
这里看明白了，就可以继续往下看哈

3.查找每门课程的直接先修课程名称

从表设计可以看出，课程的先修课名parent_id所指示的值对应用name的值，这里介绍两种方法。

方法1：使用自连接，查找直接先修课名称
将course作自连接时，连接的条件是course(c1)的parent_id和course(c2)的id值相同。由于id为1，2的课程没有先修课，为了显示出所有课程，这里需要用到left join。

#自连接
select c1.id,c1.name,c1.parent_id,c2.name
from course c1 left join course c2  #对同一张表使用自连接时必须要指定表的别名，以区分为不同的表对象
	on c1.parent_id = c2.id

方法2：子查询用于相关计算，查找直接先修课名称
子查询相关计算的方法比较好理解，当外层查询获得一个parent_id是传递到子查询求出先修课程对应的课程名。实现代码如下：

#子查询作为相关计算
select id,name,parent_id,
       (select name from course 
         where id = c.parent_id) as parent_name
from course c; 

两种方法运行的结果相同，结果如下图

4.查找出指定课程的所有先修课程名称

如何找出某一门课程的所有先修课(也就是回溯到最先修课程)呢？比如“数据库高级应用”的先修课就是“数据库应用基础”—>“C语言程序设计”。
要在这一个select语句中指出所有的一对多目录层次的所有祖先。需要在select用到mysql中的用户变量。
ps: mysql中的变量也分成若干类别，这里仅介绍用户变量

4.1了解mysql中的用户变量

mysql的用户变量只作用在当前连接的当前会话中。用@标识。比如@x，表示用户变量x, 在mysql中用户变量不需要定义，直接可以使用，其赋值方式可用使用set或select查询。
比如:

#mysql定义变量本地变量
#set赋值,可以使用=，:=两种形式赋值
set @x := 10;    #定义变量var
select @x, @x*2 as double_x; #输出 10, 20
#select赋值，只能使用:=
select @y := 10, @y*2 as double_y; #输出 10, 20

自已在mysql中运行看效果哈，这里不截图了。

4.2找出指定某课程的ID为8的前修课程id

select @id,(SELECT @id := parent_id 
            FROM course 
            WHERE id = @id) AS parent_id
from (select @id := 8) tmp,course
where @id <> 0;

从这个查询结果可以看到，我们找出了id为8的课程的所有先修课程的id,即3，1。
看明白这个查询没？

接下来我们解析下这个查询。
第1步：分析查询的数据源:

select *
from (select @id := 8) tmp,course

定义用户变量@id，其值为8，将查询结果作为临时表tmp与course表作笛卡儿积。结果如下图所示。

第2步：分析parent_id列

有没有觉得眼熟？Bingo，这里就是一个子查询用于相关计算，不同于我们平常写的相关子查询的是，这里每读取一行记录都会更新用户变量@id。通过不断迭代@id的值，可以找出@id为8的所有课程的先修祖先。
当找到的先修课程的parent_id值为0时，后面的记录就都为0了。
如果要把每个先修课程名称都显示出来，可不可以直接在上面查询的select的列表中添加一个name列呢？
可以尝试下哦？如果不行应该怎么做?这个问题大家可以回复在评论区，答对了送你一朵大红花。
以下我介绍一种方法

4.3找出指定某课程的ID为8的前修课程的课程名称

这里只需要将上面查询的结果集作一个派生表来使用，再与course表进行连接，就可以找出所有课程名称。

select id,name
	from (
		select @id as _id, (SELECT @id := parent_id 
					FROM course 
					WHERE id = _id) AS parent_id
		from (select @id := 8 ) vars,course) as tt
	join course c on tt._id = c.id

ps: 当然也可以用子查询哦，可以自己试试
查询结果如下：

如果要将这些先修课拼成一个字符串。就可以这样做：

select group_concat(name order by id separator ' / ') as 先修课程
from (select id,name
	from (
		select @id as _id, (SELECT @id := parent_id 
					FROM course 
					WHERE id = _id) AS parent_id
		from (select @id := 8 ) vars,course) as tt
	join course c on tt._id = c.id
	) as t

结果如下：

若要求出每门课程的所有先修课。用一个select就难以实现了，实际中可以将查询逻辑封装到存储过程中实现。