文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

数据库外连接及MySQL实现

2024-04-02 19:55

关注

MySQL查询分为内连接查询和外连接查询,他们的区别在于:内连接查询的两个表示对等关系,根据条件进行匹配;外连接是以某一个表为主,两一个表根据条件进行关联。外连接分为左外连接、右外连接和全外连接。本文重点介绍各外连接的思想,以及如何实现全外连接,并举例。

左外连接

左外连接以左边表为基础,根据条件,将右边表附属到左边表,语法:SELECT * FROM A LEFT JOIN B ON condition。几何图形关系如下图,即查询结果集除了A表所有数据外,还包含满足条件的B表数据:

数据库外连接及MySQL实现

右外连接

右外连接以右边表为基础,根据条件,将左边表附属到右边表,语法:SELECT * FROM A RIGHT JOIN B ON condition。几何图形关系如下图,即查询结果集除了B表所有数据外,还包含满足条件的A表数据:

数据库外连接及MySQL实现

全外连接

全外连接是除了能够根据条件匹配得到的数据,还包含左右两表中都不匹配的数据(默认应为null),应用全外连接的情况一般都有一个联系左右两表的主线。几何关系如下图所示,对应A和B的并集(去重):

数据库外连接及MySQL实现

但不幸的是MySQL不支持全外连接,那在需要全外连接查询的情况下,如何实现呢?最常见的是左连接与右连接合并。

实例

项目中存在这样的场景:某项任务task具有2种不同的状态todo和done,分别存储在todolist和donelist表中,任务存储在task表中,现在需要统计每个task的已处理和未处理情况。首先先到了全外连接,那么如何实现呢?

举例实现表结构如下:

数据库外连接及MySQL实现

实现四种方法:

1、左连接,右连接,合并;(需保持两个结果集结构一致)

数据库外连接及MySQL实现

 1 SELECT 2     A.id AS Aid, 3     B.id AS Bid, 4     A.taskid tid 5 FROM 6     ( 7         SELECT 8             * 9         FROM10             todolist11         WHERE12             todolist.user = '张三'13     ) A14 LEFT JOIN (15     SELECT16         *17     FROM18         donelist19     WHERE20         donelist.user = '张三'21 ) B ON A.taskid = B.taskid

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 1 SELECT 2     A.id AS Aid, 3     B.id AS Bid, 4     A.taskid tid 5 FROM 6     ( 7         SELECT 8             * 9         FROM10             todolist11         WHERE12             todolist.user = '张三'13     ) A14 RIGHT JOIN (15     SELECT16         *17     FROM18         donelist19     WHERE20         donelist.user = '张三'21 ) B ON A.taskid = B.taskid

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 1 SELECT 2     SUM(IF(Aid IS NOT NULL, 1, 0)) todo, 3     SUM(IF(Bid IS NOT NULL, 1, 0)) done, 4     task.name 5 FROM 6     ( 7         SELECT 8             A.id AS Aid, 9             B.id AS Bid,10             A.taskid tid11         FROM12             (13                 SELECT14                     *15                 FROM16                     todolist17                 WHERE18                     todolist.user = '张三'19             ) A20         LEFT JOIN (21             SELECT22                 *23             FROM24                 donelist25             WHERE26                 donelist.user = '张三'27         ) B ON A.taskid = B.taskid28         UNION29             SELECT30                 A.id AS Aid,31                 B.id AS Bid,32                 B.taskid tid33             FROM34                 (35                     SELECT36                         *37                     FROM38                         todolist39                     WHERE40                         todolist.user = '张三'41                 ) A42             RIGHT JOIN (43                 SELECT44                     *45                 FROM46                     donelist47                 WHERE48                     donelist.user = '张三'49             ) B ON A.taskid = B.taskid50     ) AS AB51 INNER JOIN task ON task.id = AB.tid52 GROUP BY53     task.name

数据库外连接及MySQL实现

 运行结果如下表,实现全外连接:

数据库外连接及MySQL实现

2、A+B左连接,B-A去除左连接到A的记录,然后合并两个结果集;(需保持两个结果集结构一致)

这是另一种实现全外连接的方式,即先查询A B的左连接,然后查询B中去除左连接到A的记录,最后合并(A代表todolist,B代表donelist):

数据库外连接及MySQL实现

 1 SELECT 2     1 AS todo, 3     CASE 4 WHEN B.id IS NOT NULL THEN 5     1 6 ELSE 7     0 8 END AS done, 9  A.taskid tid 
10 FROM11     (12         SELECT13             *14         FROM15             todolist16         WHERE17             todolist.user = '张三'18     ) A19 LEFT JOIN (20     SELECT21         *22     FROM23         donelist24     WHERE25         donelist.user = '张三'26 ) B ON A.taskid = B.taskid

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 1 SELECT 2     0 AS todo, 3     1 AS done, 4     donelist.taskid tid 5 FROM 6     donelist 7 WHERE 8     donelist.user = '张三' 9 AND NOT EXISTS (10     SELECT11         *12     FROM13         todolist14     WHERE15         todolist.taskid = donelist.taskid16     AND donelist.user = '张三'17     AND odolist.user = donelist.user18 )

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 1 SELECT 2     SUM(AB.todo) todo, 3     SUM(AB.done) done, 4     task.name 5 FROM 6     ( 7         SELECT 8             1 AS todo, 9             CASE10         WHEN B.id IS NOT NULL THEN11             112         ELSE13             014         END AS done,15         A.taskid tid16     FROM17         (18             SELECT19                 *20             FROM21                 todolist22             WHERE23                 todolist.user = '张三'24         ) A25     LEFT JOIN (26         SELECT27             *28         FROM29             donelist30         WHERE31             donelist.user = '张三'32     ) B ON A.taskid = B.taskid33     UNION34         SELECT35             0 AS todo,36             1 AS done,37             donelist.taskid tid38         FROM39             donelist40         WHERE41             donelist.user = '张三'42         AND NOT EXISTS (43             SELECT44                 *45             FROM46                 todolist47             WHERE48                 todolist.taskid = donelist.taskid49             AND donelist.user = '张三'50             AND odolist.user = donelist.user51         )52     ) AB53 INNER JOIN task ON task.id = AB.tid54 GROUP BY55     task.name

数据库外连接及MySQL实现

 结果同上

3、以task表为根本,将A和B表左连接,实现查询;

该方法的思想是,不管A和B表有什么关系,他们都跟作为主线的表task相关,只需要将A和B表与task表进行左连接,得到连接后的数据集,即为最后需要查询的结果集。SQL代码如下:

数据库外连接及MySQL实现

 1 SELECT 2     SUM(AB.todo) AS todo, 3     SUM(AB.done) AS done, 4     task.name 5 FROM 6     ( 7         SELECT 8             task.name, 9             CASE10         WHEN A.id IS NULL THEN11             012         ELSE13             114         END AS todo,15         CASE16     WHEN B.id IS NULL THEN17         018     ELSE19         120     END AS done21     FROM22         task23     LEFT JOIN (24         SELECT25             *26         FROM27             todolist28         WHERE29             todolist.user = '张三'30     ) A ON A.taskid = task.id31     LEFT JOIN (32         SELECT33             *34         FROM35             donelist36         WHERE37             donelist.user = '张三'38     ) B ON B.taskid = task.id39     WHERE40         A.id IS NOT NULL41     OR B.id IS NOT NULL42     ) AB43 GROUP BY44     task.name

数据库外连接及MySQL实现

 查询结果同上,但这种方法存在一定的缺陷,即当主线表(task表)特别大的时候,性能会比较差。

4、A表查a状态,B表查b状态,然后合并;(需保持两个结果集结构一致)

该方法是不管A和B表的关系,现根据条件查询,然后在合并。SQL语句如下:

数据库外连接及MySQL实现

 1 SELECT 2     SUM(A.todo) todo, 3     SUM(A.done) done, 4     task.name 5 FROM 6     ( 7         SELECT 8             1 todo, 9             0 done,10             todolist.taskid tid11         FROM12             todolist13         WHERE14             todolist.user = '张三'15         UNION ALL16             SELECT17                 0 todo,18                 1 done,19                 donelist.taskid tid20             FROM21                 donelist22             WHERE23                 donelist.user = '张三'24     ) A25 INNER JOIN task ON task.id = A.tid26 GROUP BY27     task.name

数据库外连接及MySQL实现

 查询结果同上。



阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯