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, 具体结构问题具体分析.
参考
1,left join 百度百科
2,left join 的用法 https://www.jianshu.com/p/db050b8914b2
3,SQL 中 distinct 的用法
只要自己变优秀了, 其他的事情才会跟着好起来 (少将 15)
posted on 2019-05-20 23:49 雨点的名字 阅读 (...) 评论 (...) 编辑 收藏
来源: https://www.cnblogs.com/qdhxhz/p/10897315.html