等待下一个秋

  • Spark
  • Flink
  • Hive
  • 数据仓库
  • ClickHouse
  • 收徒弟
  • Java
    • Spring
    • Mybatis
    • SpringBoot
    • 面试题
  • Python
    • Python基础
    • 爬虫
    • Numpy
    • matplotlib
    • Flask
  • 技术杂谈
    • Linux知识
    • Docker
    • Git教程
    • Redis教程
    • mysql
    • 前端
    • R语言
    • 机器学习
  • 关于我
  • 其它
    • 副业挣钱
    • 资料下载
    • 资料文档
专注于Hadoop/Spark/Flink/Hive/数据仓库等
关注公众号:大数据技术派,获取更多学习资料。
  1. 首页
  2. 技术杂谈
  3. mysql
  4. 正文

MySQL自连接实例讲解

2021年10月13日 2844点热度 0人点赞 0条评论

在上一个教程中,您已经学习了如何使用INNER JOIN,LEFT JOIN或RIGHT JOIN将表连接到其他表。但是,有一种特殊情况,即将表连接到自身,这称为自连接。

进行自连接时我们需要一个机制来区分一个表的两个实例。在FROM clause(子句)中我们可以给这个表取不同的别名,然后在语句的其它需要使用到该别名的地方用dot(点)来连接该别名和字段名。 

 

MySQL自联接示例1

让我们来看看示例数据库中的employees表。

在employees表中,我们不仅存储员工的数据,还有组织结构的数据。reports_to*字段是用来存储这个雇员的管理者的ID。

为了获得整个组织结构,我们可以在employee_id和report_to这两个字段上连接 employee表本身。employees表有两个角色:一个是Manager,另一个是Direct Report。

SELECT CONCAT(m.lastname,', ',m.firstname) AS 'Manager',
       CONCAT(e.lastname,', ',e.firstname) AS 'Direct report' 
FROM employees e
INNER JOIN employees m ON m.employeeNumber = e.reportsto
ORDER BY manager;

结果如下:

在上面的例子中,我们只看到经理所管理的员工。但是,我们没有看到高层管理者,因为他们的名字被INNER JOIN子句过滤掉了。高层管理人员就是没有任何经理或report_to的值为NULL。

让我们修改上述INNER JOIN子句为LEFT JOIN子句查询包括高层管理者。我们还需要使用IFNULL功能在经理姓名为NULL时,显示高级经理(CEO)的姓名

SELECT 
    IFNULL(CONCAT(m.lastname, ', ', m.firstname),
            'Top Manager') AS 'Manager',
    CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
    employees e
        LEFT JOIN
    employees m ON m.employeeNumber = e.reportsto
ORDER BY manager DESC

结果如下:

通过使用MySQL自连接,我们可以通过将customers表连接到自身来显示位于同一城市的客户列表。

SELECT
 c1.city,
 c1.customerName,
 c2.customerName
FROM
 customers c1
INNER JOIN customers c2 ON c1.city = c2.city
AND c1.customername > c2.customerName
ORDER BY
 c1.city;

结果如下:

我们通过以下连接条件将customers表连接到自身:

  • c1.city = c2.city 以确保两个客户具有相同的城市
  • c.customerName > c2.customerName 以确保我们不会得到同一个客户。

 

MySQL自联接示例2

我们在这里同样给出两个表来对自连接进行解释。 

  1. 车站表: stops(id, name) 
  2. 公交线路表: route(num, company, pos, stop) 

一、对公交线路表route进行自连接。 

SELECT * FROM route R1, route R2 
WHERE R1.num=R2.num AND R1.company=R2.company 

我们route表用字段(num, company)来进行自连接. 结果是什么意思呢? 

你可以知道每条公交线路的任意两个可联通的车站。 

二、用stop字段来对route(公交线路表)进行自连接。 
 

SELECT * FROM route R1, route R2 
WHERE R1.stop=R2.stop; 

查询的结果就是共用同一车站的所有公交线。这个结果对换乘是不是很有意义呢。 

从这两个例子我们可以看出,自连接的语法结构很简单,但语意结果往往不是那么容易理解。就我们这里所列出的两个表,如果运用得当,能解决很多实际问题, 

例如,任意两个站点之间如何换乘。 

SELECT R1.company, R1.num 
FROM route R1, route R2, stops S1, stops S2 
WHERE R1.num=R2.num AND R1.company=R2.company 
AND R1.stop=S1.id AND R2.stop=S2.id 
AND S1.name='Craiglockhart' 
AND S2.name='Tollcross'

 

在本教程中,我们向您介绍了MySQL自连接,希望对大家的学习有一定的帮助。


标签: mysql
最后更新:2021年10月13日

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

点赞
< 上一篇
下一篇 >

文章评论

取消回复

搜一搜
微信
最新 热点 随机
最新 热点 随机
深入理解ClickHouse跳数索引(二级索引) ClickHouse主键索引(一级稀疏索引)最佳实践 Java和Python操作Clickhouse ChatGPT 注册教程 ChatGPT可以做什么 ClickHouse 自定义分区键
Redis 字符串 Matplotlib 图像教程 MySQL示例数据库下载 Windows10总是自动切换为繁体 数据类型 Zookeeper面试题总结
标签聚合
数据仓库 Flink Redis 大数据 Hive R语言 Python 算法 挣钱 书籍 mysql Java
文章归档
  • 2023年4月
  • 2023年3月
  • 2023年2月
  • 2022年12月
  • 2022年11月
  • 2022年9月
  • 2022年7月
  • 2022年6月
  • 2022年5月
  • 2022年4月
  • 2022年3月
  • 2022年2月
  • 2022年1月
  • 2021年12月
  • 2021年11月
  • 2021年10月
  • 2021年9月
  • 2021年8月
  • 2021年6月
  • 2021年5月
  • 2021年4月
  • 2021年3月
  • 2021年2月
  • 2021年1月
  • 2020年12月
  • 2020年11月
  • 2020年10月
  • 2020年9月
  • 2020年8月
  • 2020年7月
  • 2020年5月
  • 2020年4月
  • 2020年1月
  • 2019年9月
  • 2019年8月
  • 2019年7月
  • 2019年6月
  • 2019年5月
  • 2019年4月
  • 2019年3月
  • 2019年1月
  • 2018年12月
  • 2017年5月

©2022 ikeguang.com. 保留所有权利。

鄂ICP备2020019097号-1

鄂公网安备 42032202000160号