纪录一次left join一对多关系而引起的BUG

纪录一次left join一对多关系而引起的BUG

MySQL（11）---纪录一次left join一对多关系而引起的bug

BUG背景 我们有一个订单表 和 一个 物流表 它们通过 订单ID 进行一对一的关系绑定。但是由于物流表在保存订单信息的时候没有做判断该订单是否已经有物流信息，
这就变成同一个订单id在物流表中存在多条数据，也就变成了本来订单表只有100条纪录，而left join 物流表后，所查询的订单数据远远大于100条。
总结 趁着上面这个问题，自己来复习下join语句 和 distinct关键字，同时说明如何解决就算关联是一对多，但我还是想只显示100条订单数据的方法。

一、理论

先再讲下关联表查询的几种表达式，网上找了一张图，通过这张图就能理解所有关联查询的含义。

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 。
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录。
inner join(等值连接) 只返回两个表中联结字段相等的行。

二、left join一对一和一对多

1、一对一关联表查询

业务逻辑1 有两张表，一张商品表、一张商品订单表回显订单列表的时候需要订单表关联商品表，如下

1）商品表

DROP TABLE IF EXISTS `t_product`;
CREATE TABLE `t_product` (
  `product_id` char(32) NOT NULL DEFAULT '' COMMENT '主键ID',
  `pro_name` varchar(64) DEFAULT NULL COMMENT '商品名称',
  `cash` double(10,2) DEFAULT '0.00' COMMENT '商品价格',
  `pro_code` varchar(32) DEFAULT NULL COMMENT '商品编号',
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';

INSERT INTO `t_product` (`product_id`, `pro_name`, `cash`, `pro_code`)
VALUES
    ('1','小米',888.00,'001'),
    ('2','华为',1888.00,'002');

2) 订单表

DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `order_id` char(32) NOT NULL DEFAULT '' COMMENT '主键ID',
  `product_id` char(32) DEFAULT NULL COMMENT '商品ID',
  `sale_amount` double(16,2) DEFAULT '0.00' COMMENT '订单金额',
  `order_number` varchar(40) DEFAULT NULL COMMENT '订单编码',
  `status` int(2) DEFAULT '1' COMMENT '订单状态 0订单无效1兑换功成2、已发货',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

INSERT INTO `t_order` (`order_id`, `product_id`, `sale_amount`, `order_number`, `status`)
VALUES
    ('1','1',888.00,'001001',1),
    ('2','2',1888.00,'001002',1);

3) 关联查询

这里需要展示订单列表，订单列表中当然需要展示商品信息。

select o.`order_id`,o.`sale_amount`,p.`pro_name` from t_order o left join t_product p on o.`product_id`=p.`product_id`;

运行结果

这两张表不可能是一对多的关系，因为左表关联右表的主键ID,所有右表不可能出现多条纪录。

2、left join有一对多关联查询

业务逻辑2 这里是逻辑也是有两张表，一张订单表、一张物流表。订单表和上面一样，数据也一致。

物流表

DROP TABLE IF EXISTS `t_logistics`;
CREATE TABLE `t_logistics` (
  `logistics_id` char(32) NOT NULL DEFAULT '' COMMENT '主键ID',
  `order_id` char(32) DEFAULT NULL COMMENT '订单ID',
  `logistics_company_name` varchar(32) DEFAULT NULL COMMENT '物流公司名称',
  `courier_number` varchar(32) DEFAULT NULL COMMENT '快递单号',
  PRIMARY KEY (`logistics_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物流信息表';

INSERT INTO `t_logistics` (`logistics_id`, `order_id`, `logistics_company_name`, `courier_number`)
VALUES
    ('1','1','顺丰','001'),
    ('2','1','顺丰','002');
    ('3','2','中通','003');

注意 这张表数据是有问题的，因为不可能一个订单同时有两条物流信息，但是你不能完全排除这条表里存在两条相同订单编号，因为左表绑定的不是右表的主键ID,这可能就是保留物流信息的时候没有判断该订单已经保存物流信息，而引起的数据重复问题。

那么这个时候问题来了。

select o.`order_id`,o.`sale_amount`,l.`logistics_company_name` from t_order o left join t_logistics l on o.`order_id`=l.`order_id`;

运行结果

我们发现，订单列表已经有三条纪录，但按照常理应该展示两条。

注意 所以从这里我们可以得知，如果你在left join 时，需要显示的数据的左表数据不能重复时，那么就需要 on 后面的表它们的对应关系是一对一的关系。显然这里对于order_id为1所对应的物流表信息是一对多的关系。

三、如何解决一对多的问题

一对多并不一定是问题，主要还是看表与表之间的关系。比如：
A表是用户表，B表是订单表。自然也就想到了一个用户可能多次下单。我们假设B表中的用户id在A表中匹配到50个用户id，但是这50个用户id总订单数是500个。这就是合理的一对多关系。

那么如果你业务逻辑肯定显示一对一的关系，而表关系确实一对多的关系，就像上面的订单表和物流表一样。怎么解决，这里有两种解决方案。

1、group by

关键点 把一对多的问题转化成聚合查询

select o.`order_id`,o.`sale_amount`,l.`logistics_company_name` from t_order o left join t_logistics l on o.`order_id`=l.`order_id` group by o.`order_id`;

2、distinct

select distinct o.`order_id`,o.`sale_amount`,l.`logistics_company_name` from t_order o left join t_logistics l on o.`order_id`=l.`order_id`;

它所得的的结果和上面是一样的。

3、group by 和 distinct 比较

1）、不同

• distinct需要将col列中的全部内容都存储在一个内存中，可以理解为一个hash结构，key为col的值，最后计算hash结构中有多少个key即可得到结果。很明显，需要将所有不同的值都存起来。内存消耗可能较大。
• 而group by的方式是先将col排序。而数据库中的group一般使用sort的方法，即数据库会先对col进行排序。而排序的基本理论是，时间复杂为nlogn，空间为1。然后只要单纯的计数就可以了。优点是空间复杂度小，缺点是要进行一次排序，执行时间会较长。

2）、使用场景

数据分布	去重方式	原因
离散	group	distinct空间占用较大，在时间复杂度允许的情况下，group 可以发挥空间复杂度优势
集中	distinct	distinct空间占用较小，可以发挥时间复杂度优势

3）、两个极端

• 数据列的所有数据都一样，即去重计数的结果为1时，用distinct最佳。
• 如果数据列唯一，没有相同数值，用group 最好。

四、distinct

1、作用于单列

select distinct name from A   #name去重

2、作用于多列

select distinct name, age from A  #根据name和age两个字段来去重的

3、COUNT统计

select count(distinct name) from A;   #表中name去重后的数目

注意： count是不能统计多个字段的，下面的SQL在SQL Server和Access中都无法运行。

若想使用多个字段，请使用嵌套查询，如下：

select count(*) from (select distinct name, age from A) AS B;

4、distinct必须放在开头

select age, distinct name from A;   #会提示错误，因为distinct必须放在开头

补充

1、能用inner join 尽量用inner join。
2、重复数据可能是表结构一对多造成的，这种情况往往是有意义的，比如订单和订单商品明细，算总价的时候，是需要sum多个明细的。
3、如果一对多的多确实没有意义，那就可以考虑用group by 或者 distinct。
4、具体结构问题具体分析。