mysql连续左连接(left join)和多层从属子表的查询

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表

student表

teacher表

teacher表

desk表

desk表

desktype表

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  * 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 '座位号', 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 '老师名称' 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

哈哈

  • 13
    点赞
  • 40
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值