left join 在做报表查询的时候很常用。
学连接查询的时候,感觉很简单,并且印象不深刻。
实际使用过程中,才遇到了困难。
在网上查询的过程中,了解到
问题1:是否可以连续left join,可以。
问题2:left join 的连接顺序,
select * from A
left join B ON A.id=B.id
left join C ON A.id=C.id
A先与B按id进行左连接,B的字段序列追加到A的字段序列后面,在内存中形成D( =A+B ),
然后D与C(按A的id和C的id)进行左连接,C的字段序列追加到D的字段序列的后面,在内存中形成E(=D+C=A+B+C),
连接查询(JOIN)是字段序列的增加,联合(UNION)查询是字段记录的追加,这一点要相互区别。
网上很多例子并没有涉及到多层级从属表的示例,故在此做了个例子,或许不太鲜明,但也还凑合。愿有心者可以有所体会和收获,欢迎勘误。
问题3:多层级从属表是否也可以用连续的left join?可以
下面做了一个例子,希望,有像我一样的初学者可以从中了解left join的用法
student 表是主表,有id,name,email,t_id(老师id),d_id(桌子id)
teacher表是student的从属表1,有id,t_name,t_email
desk表是student的从属表2,有id,position,type(桌子类型)
desktype表是桌子类型的字典表从属于desk表
student-------s.t_id=t.id------>teacher
|--------s.d_id=d.id---->desk-----d.type=dt.id_dtype---->desktype,学生,桌子,和桌类型表形成了多层从属关系
student表
teacher表
desk表
desktype表
通过如下代码段
select
*
from student as s
left join teacher as t on s.t_id=t.id
left join desk as d on s.d_id=d.id
left join desktype as dt on d.type=dt.id_dtype
select
s.name as '学生名称',
t.t_name as '老师名称',
d.id as '座位号',
dt.desc as '座位描述'
from student as s
left join teacher as t on s.t_id=t.id
left join desk as d on s.d_id=d.id
left join desktype as dt on d.type=dt.id_dtype
select
s.name as '学生名称',
t.t_name as '老师名称'
d.id as '桌位号',
d.desc as '桌类型'
from student as s
left join teacher as t on s.t_id=t.id
left join
(
select
d.id,
dt.desc
from desk as d
left join desktype as dt on d.type=dt.id_dtype
) as d on d.id=s.d_id
select
s.name as '学生名称',
t.t_name as '老师名称'
from student as s
left join teacher as t on s.t_id=t.id
left join
(
select
d.id,
dt.desc
from desk as d
left join desktype as dt on d.type=dt.id_dtype
) as d on d.id=s.d_id