背景
在分销系统中,公司配置商品的默认利润,每个分销商户一开始都会使用这套默认配置,
但是商户自己想改变默认利润,就会生成一条记录到商户佣金表。
需求
查询指定商户的所有商品利润,这里就以商户id为2为例
但是有几点要注意
- 只有自定义利润才会生成记录,其他商品没有更改利润的,就还是去默认利润表查询
- 商户佣金表有记录就说明这个商品是覆盖了默认表的利润
- 查询时查有与默认利润id管理的数据是自定义利润,剩下没有关联记录的就是去默认利润表查
表数据
默认利润表
id | product_name | profit |
---|---|---|
1 | 1元话费 | 0.5 |
2 | 2元话费 | 0.6 |
3 | 3元话费 | 1 |
4 | 4元话费 | 1 |
id | dis_mrch_id | profit_default_id | profit |
---|---|---|---|
1 | 1 | 1 | 1 |
2 | 1 | 2 | 1 |
3 | 1 | 3 | 1 |
4 | 2 | 2 | 1 |
5 | 2 | 3 | 1 |
6 | 3 | 1 | 1 |
开始写SQL
先来个统一称呼a表指默认利润表,b表指商户佣金表
预想结果
a表:产品名称 | a表:默认利润 | b表:商户id | b表:自定义利润 |
---|---|---|---|
1元话费 | 0.5 | ||
2元话费 | 0.6 | 2 | 1 |
3元话费 | 1 | 2 | 1 |
4元话费 | 1 |
想要的是没有关联的记录b表为null显示,这样才能判断商户哪个产品是自定义利润的,即通过判断自定义利润字段不为null
即可
错误思路
我先从我最先开始的错误想法说起
刚开始想着用 默认利润表 left join 商户佣金表,如下
SELECT
a.product_name 'a表:产品名称',
a.profit 'a表:默认利润',
b.dis_mrch_id 'b表:商户id',
b.profit 'b表:自定义利润'
FROM
profit_template a
LEFT JOIN profit_mrch b ON a.id = b.profit_default_id
a表:产品名称 | a表:默认利润 | b表:商户id | b表:自定义利润 |
---|---|---|---|
1元话费 | 0.5 | 1 | 1 |
2元话费 | 0.6 | 1 | 1 |
3元话费 | 1 | 1 | 1 |
2元话费 | 0.6 | 2 | 1 |
3元话费 | 1 | 2 | 1 |
1元话费 | 0.5 | 3 | 1 |
4元话费 | 1 |
然后在加个where语句就行,这里为了解释错误就不加上了。
为啥商户id为2的没有1元话费和4元话费为null的记录呢?
原来是因为我把LEFT JOIN的用法理解错了,一直看网上都说是保留左表所有数据,想成是a其中几条记录与b关联会显示,但是剩下几条没关联,左表还是保留下来,右表就会显示null,就像上面错误sql结果最后一条4元话费,后面为b表都为null。
虽然商户id为2只与a表有两条关联记录,剩下两条没关联,我以为应该会显示出来,但是这是错误的,因为LEFT JOIN保留左表,右表为null的条件是只要有一条与a表关联的记录,就不会出现b表为null情况,而4元话费因为b表没有任何一条有与他关联的记录所以保留下来。
这才是问题核心所在,那咋解决呢,其实很简单,看下面。
正确思路
既然没有关联才会保留b表为null,而我们想要的是指定商户没有关联就显示null,那么就先把商户自定义利润表的指定商户数据查出来,再去关联,这样才不会被其他商户关联的默认配置id所干扰。
例如:商户id为1有与1元话费默认配置关联,如果不进行子查询,把商户2的数据先取出来,直接a left join b
再去where 商户id=2,一元话费就不会显示为null,因为商户1与他有关联了。
SELECT
a.product_name 'a表:产品名称',
a.profit 'a表:默认利润',
b.dis_mrch_id 'b表:商户id',
b.profit 'b表:自定义利润'
FROM
profit_template a
LEFT JOIN ( SELECT * FROM profit_mrch WHERE dis_mrch_id = 2 ) b ON a.id = b.profit_default_id
顺带说一句,在a left join b中a为驱动表,b被驱动表,所以a表示逐行扫描与b相匹配的记录,所以b有索引就会快很多,既然是这样我们就在b表加索引,如果是主键会更好。
原因如下:下面内容参考:聚集索引与非聚集索引的总结
聚集(clustered)索引,也叫聚簇索引。
定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。
地址 | id | username | score |
---|---|---|---|
0x01 | 1 | 小明 | 90 |
0x02 | 2 | 小红 | 80 |
0x03 | 3 | 小华 | 92 |
.. | .. | .. | .. |
0xff | 256 | 小英 | 70 |
注:第一列的地址表示该行数据在磁盘中的物理地址,后面三列才是我们SQL里面用的表里的列,其中id是主键,建立了聚集索引。
结合上面的表格就可以理解这句话了吧:数据行的物理顺序与列值的顺序相同,如果我们查询id比较靠后的数据,那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同,所以也就只能建立一个聚集索引了。