加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 大数据 > 正文

子查询（in not ）转化为连接查询的方法（很神奇的，BI可以借鉴

发布时间：2020-12-14 02:50:12 所属栏目：大数据 来源：网络整理
导读：引用 ?? 我们进行数据查询的时候极少有可能就在一张表里就能得到想要的数据，不可避免得会用到子查询或者连接查询，很多时候我们很轻松自然得会想到子查询的方法，但是子查询往往效率比较低，而转换成连接查询是一种很好的优化方式。? ??? 子查询转换成连接
引用
?? 我们进行数据查询的时候极少有可能就在一张表里就能得到想要的数据，不可避免得会用到子查询或者连接查询，很多时候我们很轻松自然得会想到子查询的方法，但是子查询往往效率比较低，而转换成连接查询是一种很好的优化方式。?

??? 子查询转换成连接查询又可以分为两种情况，一种是不带聚合的子查询转换，另一种就是带有聚合函数的转换?

一、不带聚合函数的子查询转换：?

以下是一组测试数据：?
Java代码?

?

  1. Sql代码?? ??
  2. use?mytest;??? ??
  3. drop?table??if?exists?jobs;??? ??
  4. CREATE?TABLE?jobs(??? ??
  5. ????employee?varchar(30),??? ??
  6. ????title?varchar(30)??? ??
  7. );??? ??
  8. drop?table?if?exists?ranks;??? ??
  9. CREATE?TABLE?ranks(??? ??
  10. ????rank?varchar(if?exists?salary;??? ??
  11. CREATE?TABLE?salary(??? ??
  12. ????payment?int(11)??? ??
  13. ?? ??
  14. insert?into?jobs?values('张三','经理'),('李四','总经理'),('王五','总经理助理');??? ??
  15. insert?into?ranks?values('经理','三'),('总经理','一'),('总经理助理','二');??? ??
  16. insert?into?salary?values('一',20000),('二',0); background-color:inherit">8000),('三',0); background-color:inherit">7000),('四',0); background-color:inherit">7000);?? ??
  17. ??
  18. use?mytest; ??
  19. if?exists?jobs; ??
  20. CREATE?TABLE?jobs( ??
  21. 30) ??
  22. ); ??
  23. if?exists?ranks; ??
  24. CREATE?TABLE?ranks( ??
  25. if?exists?salary; ??
  26. CREATE?TABLE?salary( ??
  27. 11) ??
  28. '总经理助理'); ??
  29. '二'); ??
  30. 7000);??
[java]? view plain copy
  1. Sql代码????
  2. use?mytest;?????
  3. drop?table??if?exists?jobs;?????
  4. CREATE?TABLE?jobs(?????
  5. ????employee?varchar(30),?????
  6. ????title?varchar(30)?????
  7. );?????
  8. drop?table?if?exists?ranks;?????
  9. CREATE?TABLE?ranks(?????
  10. ????rank?varchar(30)?????
  11. );?????
  12. drop?table?if?exists?salary;?????
  13. CREATE?TABLE?salary(?????
  14. ????payment?int(11)?????
  15. ????
  16. insert?into?jobs?values('张三','经理'),('李四','总经理'),('王五','总经理助理');?????
  17. insert?into?ranks?values('经理','三'),('总经理','一'),('总经理助理','二');?????
  18. insert?into?salary?values('一',0); background-color:inherit">20000),('二',0); background-color:inherit">8000),('三',0); background-color:inherit">7000),('四',0); background-color:inherit">7000);????
  19. ??
  20. use?mytest;??
  21. drop?table??if?exists?jobs;??
  22. CREATE?TABLE?jobs(??
  23. ????employee?varchar( ????title?varchar(30)??
  24. );??
  25. if?exists?ranks;??
  26. CREATE?TABLE?ranks(??
  27. ????rank?varchar(30)??
  28. );??
  29. if?exists?salary;??
  30. CREATE?TABLE?salary(??
  31. ????payment?11)??
  32. ??
  33. insert?into?jobs?values('张三','总经理助理');??
  34. insert?into?ranks?values('经理','二');??
  35. insert?into?salary?values('一',0); background-color:inherit">7000);??


?? 建立了三个表，分别是jobs员工工作表，记录了员工的工作，第二表ranks是岗位等级表，记录每一个工作岗位的等级，第三个表slary自然就是HR为每一个等级的定的薪资标准了。?

??? 现在要知道张三的工资是多少，就需要使用三张表才能得到数据，?
使用子查询的方法如下：?

select?payment?from?salary???? ??
  • ????where?rank=(??? ??
  • ????????SELECT?rank?from?ranks???? ??
  • ????????????where?title=(??? ??
  • ????????????????SELECT?title?from?jobs???? ??
  • ????????????????????where?employee='张三')??? ??
  • );?? ??
  • select?payment?from?salary? ??
  • ????where?rank=( ??
  • ????????SELECT?rank?from?ranks? ??
  • ????????????where?title=( ??
  • ????????????????SELECT?title?from?jobs? ??
  • ????????????????????where?employee='张三') ??
  • );??
    • copy
      select?payment?from?salary??????
    1. ????where?rank=(?????
    2. ????????SELECT?rank?from?ranks??????
    3. ????????????where?title=(?????
    4. ????????????????SELECT?title?from?jobs??????
    5. ????????????????????where?employee='张三')?????
    6. );????
    7. select?payment?from?salary???
    8. ????where?rank=(??
    9. ????????SELECT?rank?from?ranks???
    10. ????????????where?title=(??
    11. ????????????????SELECT?title?from?jobs???
    12. ????????????????????where?employee='张三')??
    13. );??

    转换为连接查询的步骤大致有如下几点：?
    1、使用表名或者表别名标记所有的列，如显jobs.employee 或者j.employee；?
    2、将几个子查询的From子名中使用的相同的表用同一个名字或同一别名；?
    3、将几个Form子句放在一起；?
    4、将Select及查询的列删除；?
    5、将第一个之后的Where替换成AND?

    最后得到如下结果：?
    select?payment?from?salary?s,ranks?r,jobs?j???? ??
  • ????where?j.employee='张三'???? ??
  • ????????and?j.title?=?r.title???? ??
  • ????????and?s.rank?=?r.rank;?? ??
  • ????where?j.employee='张三'? ??
  • ????????and?j.title?=?r.title? ??
  • ????????and?s.rank?=?r.rank;??
    • copy
      ????where?j.employee='张三'??????
    1. ????????and?j.title?=?r.title??????
    2. ????????and?s.rank?=?r.rank;????
    3. select?payment?from?salary?s,jobs?j???
    4. ????where?j.employee='张三'???
    5. ????????and?j.title?=?r.title???
    6. ????????and?s.rank?=?r.rank;??

    对于需要排除某些条件的查询，如查询岗位等级表中在薪资表中没有工资级别的等级：?
    Java代码?? ??
  • select?salary.rank???? ??
  • ????from?salary???? ??
  • ????????where?rank???? ??
  • ????????????not?in(select?rank?from?ranks);?? ??
  • select?salary.rank? ??
  • ????from?salary? ??
  • ????????where?rank? ??
  • ????????????not?in(select?rank?from?ranks);??
    • copy
      Java代码????
    1. select?salary.rank??????
    2. ????from?salary??????
    3. ????????where?rank??????
    4. ????????????not?in(select?rank?from?ranks);????
    5. select?salary.rank???
    6. ????from?salary???
    7. ????????where?rank???
    8. ????????????not?in(select?rank?from?ranks);??

    使用not in、exists、not exists不失为一种好方法，但同样可以转换成连接查询。如以上的查询可以转换为：?
    ????from?salary?left?join?ranks???? ??
  • ????????on?salary.rank=ranks.rank???? ??
  • ????????????where?ranks.rank?is?null;?? ??
  • ????from?salary?left?join?ranks? ??
  • ????????on?salary.rank=ranks.rank? ??
  • null;??
    • copy
      ????from?salary?left?join?ranks??????
    1. ????????on?salary.rank=ranks.rank??????
    2. ????????????where?ranks.rank?is?null;????
    3. ????from?salary?left?join?ranks???
    4. ????????on?salary.rank=ranks.rank???
    5. ????????????where?ranks.rank?is?null;??

    二、带聚合函数的子查询向连接查询转换?

    如下测试数据，有一个订单表，记录了销售人员每天的销售记录，测试数据如下：?
    DROP?TABLE?if?exists?orders;??? ??
  • create?table?orders(??? ??
  • ????customer?varchar( ????whn?date,248)"> ????totalitems? insert?into?orders?values('jj','2010-10-10',0); background-color:inherit">5),248)"> ????????????('jj','2010-10-11',0); background-color:inherit">3),'2010-10-12',0); background-color:inherit">1),248)"> ????????????('aa',248)"> ????????????('bb',0); background-color:inherit">8),248)"> ????????????('cc',0); background-color:inherit">10);?? ??
  • if?exists?orders; ??
  • create?table?orders( ??
  • 10);??
    • copy
      DROP?TABLE?if?exists?orders;?????
    1. create?table?orders(?????
    2. ????customer?varchar( ????totalitems?11)?????
    3. insert?into?orders?values('jj','2010-10-10',0); background-color:inherit">5),248)"> ????????????('jj','2010-10-11',0); background-color:inherit">3),108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> ????????????('jj','2010-10-12',0); background-color:inherit">1),248)"> ????????????('aa',108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> ????????????('bb',0); background-color:inherit">8),248)"> ????????????('cc',0); background-color:inherit">10);????
    4. if?exists?orders;??
    5. create?table?orders(??
    6. 10);??

    需要查询每一个销售员最高销售额的日期及销售额时，必然用的聚合函数MAX，以下是最容易想到的查询方式：?
    select?customer,whn,totalitems???? ??
  • ????from?orders?o1?where?o1.totalitems=(??? ??
  • ????????SELECT?max(totalitems)???? ??
  • ????????????from?orders?o2???? ??
  • ????????????????where?o1.customer?=?o2.customer??? ??
  • ????from?orders?o1?where?o1.totalitems=( ??
  • ????????SELECT?max(totalitems)? ??
  • ????????????from?orders?o2? ??
  • ????????????????where?o1.customer?=?o2.customer ??
  • copy
      • ????from?orders?o1?where?o1.totalitems=(?????
    1. ????????SELECT?max(totalitems)??????
    2. ????????????from?orders?o2??????
    3. ????????????????where?o1.customer?=?o2.customer?????
    4. );????
    5. select?customer,totalitems???
    6. ????from?orders?o1?where?o1.totalitems=(??
    7. ????????SELECT?max(totalitems)???
    8. ????????????from?orders?o2???
    9. ????????????????where?o1.customer?=?o2.customer??
    10. );??

    此时需要对每一行订单都要进行子查询，因此代码运行速度会很慢，并且老版本的MySQL还不支持子查询，只有一个表，要改成连接查询自然就是自连接了，这里我们需要使用Having子句，?
    select?o1.*?from?orders?o1?join?orders?o2???? ??
  • ????on(o1.customer=o2.customer)???? ??
  • ????????group?by?o1.customer???? ??
  • ????????????having?o1.totalitems=max(o2.totalitems??? ??
  • select?o1.*?from?orders?o1?join?orders?o2? ??
  • ????on(o1.customer=o2.customer)? ??
  • ????????group?by?o1.customer? ??
  • ????????????having?o1.totalitems=max(o2.totalitems ??
  • copy
      • select?o1.*?from?orders?o1?join?orders?o2??????
    1. ????on(o1.customer=o2.customer)??????
    2. ????????group?by?o1.customer??????
    3. ????????????having?o1.totalitems=max(o2.totalitems?????
    4. select?o1.*?from?orders?o1?join?orders?o2???
    5. ????on(o1.customer=o2.customer)???
    6. ????????group?by?o1.customer???
    7. ????????????having?o1.totalitems=max(o2.totalitems??
    8. );??
    相信这些我们大学的时候都已经学过，但是没有真正用起来的时候总是那么容易忘记，没有实际操作和体验是感觉不到它的需要，自然也不长记性了，而写下来又是另一种记住的方式。

    （编辑：李大同）

    【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
      相关内容
        推荐文章
          站长推荐
          热点阅读

            【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

            建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

            Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同