精华内容
下载资源
问答
  • join查询再熟悉不过了,看似没有必要把它拿出来说事,但某天一学生问起了一问题,有必要拿来说下。很多同学的毕生梦想都是想进BATJ的某家公司,其中又以案例为多数,是的,不管从哪个角度来说,阿里都是一非常...
    dfd3b624a246cc37dea63948c01aa78a.png

    join查询再熟悉不过了,看似没有必要把它拿出来说事,但某天一个学生问起了一个问题,有必要拿来说下。

    很多同学的毕生梦想都是想进BATJ的某家公司,其中又以案例为多数,是的,不管从哪个角度来说,阿里都是一个非常把棒的公司,值得去挑战一把。

    回到今天的话题,一个同学(阿里的铁杆粉),问了我一个问题,在实际工作中到底应不应该用join查询,到底应该注意点什么?

    别以为这个问题很傻,如果你关注过阿里的规范,里面就有显著的一条:

    【强制】超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。

    首先,我要交代,上面这句话出自《阿里java编程规范手册》,并不是我的原创,另外我想说的是,对于上面这句话,我不管你懂不懂为什么,请按手册里面说的,强制去执行。

    不过既然有同学再问,我认为还是有必要把这个问题拿来分析下

    1. 禁止超过3表的JOIN

    如果太多的表JOIN对性能的影响是非常大的(Join的性能以及注意事项我后面再分析),这个我相信,对于绝大多数读者都是认可的,起码能做到表面上理解,但也不排除某些另外情况,比如说...

    某天,有个同学小甲,给我发个SQL语句,一方面夸夸其谈他对公司的业务,表结构了解的如何透彻。

    我打开sql文件后,一个2132行的sqL语句映入眼帘,我心里顿时(&%%¥&……),好吧,我得承认这情况并不只一个,而且他们以这个为荣,我不知道当这同学离职后,后面接手的会不会和我一样的心情。

    现在我也无力反驳他,这可能在某些特殊的情况下对他是好的(谁敢开除他,谁敢接他的手),我现在只想聊聊出现过多表关联之后怎么来解决。

    确实答案也出自《阿里java编程规范手册》

    【推荐】字段允许适当冗余,以提高查询性能,但必须考虑数据一致。

    其实虽然规范里面没说,但这是数据库设计里面非常重要的一条:

    反范式设计

    • 反范式化是针对范式化而言得,在前面介绍了数据库设计得范式
    • 所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反
    • 允许存在少量的冗余,换句话来说反范式化就是使用空间来换取时间

    举一个简单的例子:

    select a.cl1,a.cl2,a.cl3,b.cl4 from a,b where a.c1=b.c1

    上面这个sql语句查询的数据来自a,b两张表,仔细看你会发现,其中只有cl4这一列是出自b表,其他的所有都是a表,这样可以考虑建立一个c表,把c1l,cl2,cl3,cl4的数据全部关联起来,实际做的事情就是冗余了cl4这个字段。

    带来的好处呢? 查询的时候只要查询c表,效率不会提升才怪。

    2. 数据类型必须一致

    数据类型必须一致,请完全遵守,如果你定是要寻根究底钻牛角尖,那我们做个实验,做实验的前提是你要了解些许的执行计划,当然对索引也得一知半解才行。

    CREATE TABLE staffs (  id int(11) NOT NULL AUTO_INCREMENT,  name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',  age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',  pos varchar(20) NOT NULL DEFAULT '' COMMENT '职位',  add_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',  PRIMARY KEY (id),  KEY idx_staffs_nameAgePos (name,age,pos) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';INSERT INTO staffs VALUES ('1', '2000', '23', 'dev', '2019-06-25 21:41:31');

    数据和表都准备好了后,我来写一个sql语句

    select * from staffs where name = 2000
    7f90f6ba089e684bccaca640d2eb23c0.png

    很棒,结果也查询出来了,name是varchar类型, 而给出的2000是整型,这有什么问题吗?结果不是正确的吗?

    别着急,还记得前面创建了索引嘛?

     KEY idx_staffs_nameAgePos (name,age,pos) 

    使用执行计划看下

    228a1561a9b888795bb3cc1f86b9e2f7.png

    看出问题了吗?虽然我指定的name上面是索引的,但数据类型不一致,不好意思,结果是全表扫描

    如果数据类型一致呢?

    EXPLAINselect * from staffs where name = "2000"
    d595ac9ac7b5d126f7f2d741e10186af.png

    key有值,代表就是用到了索引,so,不需要再钻牛角尖了吧

    3. 保证被关联的字段需要有索引

    这句话很好理解吧,如果t1,t2表,如果sql语句

    select * from t1 straight_join t2 on (t1.a=t2.a);

    你可能会问straight_join是什么? 他和join有什么区别呢?

    好吧,你既然问到了,我就还是说下,如果你使用join,Mysql优化器可能会选择t1或者t2来作为驱动表,而我使用的是straight_join,那么关系很明确了,t1是驱动表,那t2就是被驱动表。

    既然语句知道被驱动表了,那被关联的字段就是t2.a,按照规范的要求,必须在t2表的a列上必须创建有索引。

    t1是驱动表,我从t1表里面取出数据后,再去t2表查询

    • 如果t2.a有索引,我根据索引查询就好,查询了结果合并t1表的记录返回。
    • 如果t2.a没索引,那么就尴尬了,需对t1表里面的所有数据都需要在t2表进行一次全表扫描,如果t1是1000行数据,t2是10000,那得扫描多少行呢?结果是(t1行数*t2行数)1000万。

    这个时候我相信你也已经理解为什么被关联的字段需要有索引了吧。

    展开全文
  • 【数据蒋堂】第 31 期:JOIN 简化 – 维度对齐我们先把上一期中双子对齐例子的 SQL 写出来: SELECT Orders.id, Orders.customer, A.x, B.y FROM Orders LEFT JOIN (SELECT id,SUM(price) x FROM OrderDetail ...

    【数据蒋堂】第 31 期:JOIN 简化 – 维度对齐

    c2ba34d2cd6836ed1be55540914320cd.png

    我们先把上一期中双子表对齐例子的 SQL 写出来:

     SELECT Orders.id, Orders.customer, A.x, B.y FROM Orders  LEFT JOIN (SELECT id,SUM(price) x FROM OrderDetail GROUP BY id ) A ON Orders.id=A.id  LEFT JOIN (SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B ON Orders.id=B.id  WHERE A.x > B.y SELECT Orders.id, Orders.customer, A.x, B.y FROM Orders  LEFT JOIN (SELECT id,SUM(price) x FROM OrderDetail GROUP BY id ) A ON Orders.id=A.id  LEFT JOIN (SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B ON Orders.id=B.id  WHERE A.x > B.y

    那么问题来了,这显然是个有业务意义的 JOIN,它算是前面所说的哪一类呢?

    这个 JOIN 涉及了表 Orders 和子查询 A 与 B,仔细观察会发现,子查询带有 GROUP BY id 的子句,显然,其结果集将以 id 为主键。这样,JOIN 涉及的三个表(子查询也算作是个临时表)的主键是相同的,它们是一对一的同维表,仍然在前述的范围内。

    但是,这个同维表 JOIN 却不能用上一期说的写法简化,子查询 A,B 都不能省略不写。

    可以简化书写的原因在于:我们假定事先知道数据结构中这些表之关联关系。用技术术语的说法,就是知道数据库的元数据(metadata)。而对于临时产生的子查询,显然不可能事先定义在元数据中了,这时候就必须明确指定要 JOIN 的表(子查询)。

    不过,虽然 JOIN 的表不能省略,但关联字段总是主键,已经在 GROUP BY 中写过了,就没有必要再写一遍了;而且,子查询的主键总是由 GROUP 产生,而 GROUP BY 的字段一定要被选出用于做外层 JOIN,也没必要在 GROUP 和 SELECT 中各写一次;并且这几个子查询涉及的子表是互相独立的,它们之间不会再有关联计算了,我们就可以把 GROUP 动作以及聚合式直接放到主句中,从而消除一层子查询:

     SELECT Orders.id, Orders.customer, OrderDetail.SUM(price) x, OrderParyment.SUM(amount) y  FROM Orders LEFT JOIN OrderDetail GROUP BY id LEFT JOIN OrderPayment GROUP BY id  WHERE A.x > B.y SELECT Orders.id, Orders.customer, OrderDetail.SUM(price) x, OrderParyment.SUM(amount) y  FROM Orders LEFT JOIN OrderDetail GROUP BY id LEFT JOIN OrderPayment GROUP BY id  WHERE A.x > B.y

    这里的 JOIN 和 SQL 定义的 JOIN 运算已经差别很大,完全没有笛卡尔积的意思了。而且,也不同于 SQL 的 JOIN 运算将定义在任何两个表之间,这里的 JOIN,OrderDetail 和 OrderPayment 以及 Orders 都是向共同的主键 id 靠拢,即所有表都向某一套基准维度对齐。而由于各表的维度(主键)不同,对齐时可能会有 GROUP BY,在引用该表字段时就会相应地出现聚合运算。OrderDetail 和 OrderPayment 甚至 Orders 之间都不直接发生关联,在书写运算时当然就不用关心它们之间的关系,甚至不必关心另一个表是否存在。而 SQL 那种笛卡尔积式的 JOIN 则总要找一个甚至多个表来定义关联,一旦减少或修改表时就要同时考虑关联表,增大理解难度。

    我们称这种 JOIN 称为维度对齐,它并不超出我们前面说过的三种 JOIN 范围,但确实在语法描述上会有不同,这里的 JOIN 不象 SQL 中是个动词,却更象个连词。而且,和前面三种基本 JOIN 中不会或很少发生 FULL JOIN 的情况不同,维度对齐的场景下 FULL JOIN 并不是很罕见的情况。

    虽然我们从主子表的例子抽象出维度对齐,但这种 JOIN 并不要求 JOIN 的表是主子表(事实上从上一篇的语法可知,主子表运算还不用写这么麻烦),任何多个表都可以这么关联,而且关联字段也完全不必要是主键或主键的部分。

    设有合同表,回款表和发票表:

    Contract 合同表

    id合同编号date签订日期customer客户price合同金额……

    Payment 回款表

    seq回款序号date回款日期source回款来源amount金额……

    Invoice 发票表

    code 发票编号

    date 开票日期

    customer 客户

    amount 开票金额

    现在想统计每一天的合同额、回款额以及发票额,就可以写成:

     SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount)  FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount)  FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date

    这几种 JOIN 情况还可能混合出现。

    延用上面的合同表,再有客户表和销售员表

    Customer 客户表

    |—|—|

    |id| 客户编号 |

    |name| 客户名称 |

    |area| 所在地区 |

    |…|…|

    Sales 销售员表

    id员工编号name姓名area负责地区……

    其中 Contract 表中 customer 字段是指向 Customer 表的外键。

    现在我们想统计每个地区的销售员数量及合同额:

     SELECT Sales.COUNT(1), Contract.SUM(price)  FROM Sales GROUP BY area FULL JOIN Contract GROUP BY customer.area SELECT Sales.COUNT(1), Contract.SUM(price)  FROM Sales GROUP BY area FULL JOIN Contract GROUP BY customer.area

    维度对齐可以和外键属性化的写法配合合作。

    这些例子中,最终的 JOIN 都是同维表。事实上,维度对齐还有主子表对齐的情况,不过相对罕见,我们将在后续仔细讲解维度概念时再涉及,上述写法中其实还有个小漏洞,有了明确的维度定义后才能将这个漏洞补上。

    展开全文
  • 三个表:作家表(至少三列:主键、姓名、性别)、图书表(至少三列:主键、书名、外键)、畅销书作者(有两列:作家名称、排名、每月统计top10,该表只保存最新的top10作家数据),图书表是作家表的从表,畅销书...

    f125e1ba5432ddca73642afbfad61885.png

    有三个表:作家表(至少三列:主键、姓名、性别)、图书表(至少三列:主键、书名、外键)、畅销书作者(有两列:作家名称、排名、每月统计top10,该表只保存最新的top10作家数据),图书表是作家表的从表,畅销书作者表中的数据是作家表的子集。请提供出建表语句,并编写一个sql:统计top10畅销书的作家总共写过多少本书。

    要求:①采用mysql或者orcale的语法。②建表、编写sql时做尽量多的考虑,比如:性能、存储 等。


    -- 我在写表时没有考虑性能、存储,应为已经大半年没有写过了,学的都忘了,对于畅销表应该使用了SQL编程,或者触发器什么的自动生成数据,这里没有实现。学的都还给老师了。每月统计top10,该表只保存最新的top10作家数据,这里没有使用,都是自己手动添加的数据,介意勿看。

    1. 创建如下表

    a1f0eda89fe67029da1e72fd79686a92.png

    b75b44df49ae81fadd611338ca7afc0a.png

    ca0c96c7c2a6e1ff0f72859fbe6d3583.png

    7a28b878e9c9f6a0e92f507b25d7e3aa.png

    6abe8dd4d4416b3c342a22613642282a.png

    2dcfd0e44e34ae8a0d4c52b51012ffc6.png

    SQL语句

    CREATE TABLE author(
    author_id int PRIMARY key auto_increment,
    aname VARCHAR(55) not null,
    sex VARCHAR(2)
    
    );

    book

    CREATE TABLE book (
    bid int PRIMARY key auto_increment,
    bname VARCHAR(55) not null,
    num int not null,
    author_id int ,
    FOREIGN key (author_id) REFERENCES AUTHOR(author_id)
    );

    favor

    CREATE TABLE favor(
    aname VARCHAR(10) not null,
    sort int not null
    );
    
    1. author 和 book 进行连接
    SELECT * 
    FROM author a
    JOIN book b
    WHERE a.author_id = b.author_id;

    487567a3120c2a0cc17a337dcaec3f92.png

    2.

    SELECT  c.aname, SUM(c.num) '数量'
    FROM (
    SELECT a.aname , b.bname, b.num, b.time 
    FROM author a
    JOIN book b
    WHERE a.author_id = b.author_id
    )  as c
    WHERE c.aname in (
    SELECT f.aname
    FROM favor f
    ) 
    GROUP BY c.aname
    ;

    94a32561b065c8fc076097dac8c47552.png
    大体数据实现了,但是和要求相差甚远,有点误人子弟,谨慎参考
    展开全文
  • 现在的问题在于写一个查询,要把ABC个表join在一起,让AB表没有而C表有的列都显示出来 简而言之就是: A表 B表可以根据维修id关联 B表 C表可以根据物品编号关联 A表 C表无直接对应关系 请问怎样把ABC三个表用...
  • INNER JOIN连接两个表、三个表、五个表的SQL语句 SQL INNER JOIN关键字表示在表中存在至少一个匹配时,INNER JOIN 关键字返回行。SQL INNER JOIN关键字表示在表中存在至少一个匹配时,INNER JOIN 关键字返回行。 1、...

    SQL INNER JOIN关键字表示在表中存在至少一个匹配时,INNER JOIN 关键字返回行。SQL INNER JOIN关键字表示在表中存在至少一个匹配时,INNER JOIN 关键字返回行。

    1、连接两个数据表的用法:

    FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort

    语法格式可以概括为:

    FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

    2、连接三个数据表的用法:

    FROM (Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel

    语法格式可以概括为:

    FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

    3、连接四个数据表的用法:

    FROM ((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity

    语法格式可以概括为:

    FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号

    4、连接五个数据表的用法:

    FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock

    语法格式可以概括为:

    FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

    展开全文
  • 跳至[1][全屏预览]SQL INNER JOIN关键字表示在中存在至少一匹配时,INNER JOIN 关键字返回行。1、连接两数据的用法:FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort语法...
  • SQL INNER JOIN关键字表示在中存在至少一个匹配时,INNER JOIN 关键字返回行。1、连接两个数据的用法:FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort....字段号2、连接三个数据的用法:...
  • 声明:本系列博客是根据SGG的视频整理而成,非常适合大家入门学习。 《2021年最新版大数据面试题全面开启更新》 维表是数仓中的一概念,维表中...维表可能是会不断变化的,在维表JOIN时,需指明这条记录关联维表快
  • SQL join关联三个表或多个表

    千次阅读 2014-02-16 16:26:51
    http://www.17jo.com/program/sql/base/JoinMany.html
  • 其中有三个表需要查询。三个表都是通过ID来关联的 第一个表的数据需要全部查询出来 t_article 第二个表的相关联的ID有多少条目,计算出来 t_thumbup 第三个表的相关联的ID有多少条目,计算出来 t_reply SQL如下 ...
  • https://zhuanlan.zhihu.com/p/165962937?utm_source=qq维是数仓中的一概念,维中的维度属性是观察数据的角度,在建设离线数仓的时候,通常是将维与事实进行关联构建星型模型。在实时数仓中,同样也有维...
  • 文章节选自松华老师《SQL开发与优化》专栏,想阅读更多内容请扫码订阅今天逛论坛看到了一个,奇葩需求,他想把两个表的结果连在一起显示我把需求整理如下 :下面有两个表 a 和 t12 数据分别如下现在想要的结果是现在...
  • SQL INNER JOIN关键字表示在中存在至少一匹配时,INNER JOIN 关键字返回行。 1、连接两数据的用法: FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort 语法格式可以概括为...
  • sql表连接left join,right join,inner join区别 left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 (以左表数据为基准,不足补为NULL)right join...只返回两个表中联结字段相等的行(条件...
  • 今天逛论坛看到了一个,奇葩需求,他想把两个表的结果连在一起显示我把需求整理如下 :下面有两个表 a 和 t12 数据分别如下现在想要的结果是现在我们来分析一下,怎样满足上述需求1,如果我们想把不同的表中的列横向...
  • SQL语句 用JOIN连结多个表

    万次阅读 2019-09-02 17:45:13
    连接两个数据的用法 : SELECT * FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id ; 语法格式可以概括为: ...连接三个数据的用法: SELECT * FROM (actor INNER JOIN film...
  • sql优化核心 是数据库中 解析器+优化器的工作,我觉得主要有以下几大方面:1>扫的方法(索引非索引、主键非主键、书签查、索引下推)2>关联的方法(种),关键是内存如何利用3>处理排序聚合的方法...
  • 已知有3个表分别为A,B,CA表内容:代号 品名A1 一A2 二A3 B表内容:代号 产量A1 1000A3 2000C表内容:代号 单价A2 1.05A3 1.10希望能够用SQL语句生成D表,内容为代号 品名 产量 单价A1 一 1000A2...
  • Hive的Join方式hiveCommon/Shuffle/Reduce JoinReduce Join在Hive中也叫Common Join或Shuffle Join如果两边数据量都很大,它会进行把相同key的value合在一起,正好符合我们在sql中的join,然后再去组合,如图所...
  • `full outer join` 一般在 SQL 中用于关联两个表取两个表的并集,百度搜索出来的都是用三个表嵌套取并集,尝试如果关联三个表平行关联,得出: 如果并列全关联,那么关键表与其他表如果关联主键相同的就会合并到同一...
  • 谓词下推,就是在将过滤条件下推到离数据源更近的地方,最好就是在table_scan时就能...有不同的策略inner joininner join的结果集是左和有都要满足条件,所以inner join condtion中的条件都是可以下推的,比如...
  • SQL Server Join方式

    2014-03-14 14:32:22
    参考文献 Microsoft SQL Server企业级平台管理实践  看懂SqlServer查询计划 1.测试数据准备 参考:Sql Server中的访问方式Table Scan, Index Scan, Index Seek 这篇博客中...在Sql Server中,每一个join命令,在内
  • SQL join 用于把来自两个或多个表的行结合起来。 下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。 SQL JOIN 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。
  • SQL三表联查

    2019-11-26 15:02:05
    下面三个表分别是:table1表、table2表、table3表 目标:查询A公司的所有30岁以下员工的名字。 SELECT employee_name FROM (table3 c LEFT JOIN TABLE1 a ON c.employee_id=a.employee_id ) LEFT JOIN table2 b ON...
  • 数据库常见的join方式有种:inner join, left outter join, right outter join(还有一种full join,因不常用,本文不讨论)。这种连接方式都是将两以上的通过on条件语句,拼成一

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,126
精华内容 450
关键字:

sql三个表join