补充知识：笛卡尔积演示

如何理解笛卡尔积

多表关联的原理是把关联的表进行笛卡尔积

• 先取出两张表

-- 1、先取出两表中A005和AO19的记录作为初始表
select *
from db_order.sn_order_20210412
where id in ('A005','A019');
select *
from db_order.sn_orderitem_20210412
where order_id in ('A005','A019');

sn_order_20140412

订单号	订单金额	会员号	创建时间	订单状态
A005	1200.15	5	2017/1/1 13:23	2
A019	99	12	2017/2/11 13:23	1

sn_orderitem_20210412

订单商品ID	订单号	商品ID	商品名称	商品价格	商品数量
AB004	A005	1001	A	0.15	1
AB005	A005	1004	D	200	6
AB0016	A019	1006	F	50	1
AB0017	A019	1007	G	24.5	2

•  手动进行笛卡尔积演示

手动_笛卡尔积

订单号	订单金额	会员号	创建时间	订单状态	订单商品ID	订单号	商品ID	商品名称	商品价格	商品数量
A005	1200.15	5	2017/1/1 13:23	2	AB004	A005	1001	A	0.15	1
A005	1200.15	5	2017/1/1 13:23	2	AB005	A005	1004	D	200	6
A005	1200.15	5	2017/1/1 13:23	2	AB0016	A019	1006	F	50	1
A005	1200.15	5	2017/1/1 13:23	2	AB0017	A019	1007	G	24.5	2
A019	99	12	2017/2/11 13:23	1	AB004	A005	1001	A	0.15	1
A019	99	12	2017/2/11 13:23	1	AB005	A005	1004	D	200	6
A019	99	12	2017/2/11 13:23	1	AB0016	A019	1006	F	50	1
A019	99	12	2017/2/11 13:23	1	AB0017	A019	1007	G	24.5	2

• 运用多表关联后结果比较

-- 2、利用join把两个表关联起来,注意在查询时仅取A005和A019做示范，所以需要用where语句做限制
select *
from db_order.sn_order_20210412 t1
join db_order.sn_orderitem_20210412 t2 -- on t2.order_id = t1.id       /*不带on展示的是做笛卡尔积后的结果*/
where t1.id in ('A005','A019')
and t2.order_id in ('A005','A019');   /*因为没有通过on关联，所以两个表都需要进行where限制*/

代码结果：join的原理是把两表做笛卡尔积

订单号	订单金额	会员号	创建时间	订单状态	订单商品ID	订单号	商品ID	商品名称	商品价格	商品数量
A005	1200.15	5	2017/1/1 13:23	2	AB004	A005	1001	A	0.15	1
A005	1200.15	5	2017/1/1 13:23	2	AB005	A005	1004	D	200	6
A005	1200.15	5	2017/1/1 13:23	2	AB0016	A019	1006	F	50	1
A005	1200.15	5	2017/1/1 13:23	2	AB0017	A019	1007	G	24.5	2
A019	99	12	2017/2/11 13:23	1	AB004	A005	1001	A	0.15	1
A019	99	12	2017/2/11 13:23	1	AB005	A005	1004	D	200	6
A019	99	12	2017/2/11 13:23	1	AB0016	A019	1006	F	50	1
A019	99	12	2017/2/11 13:23	1	AB0017	A019	1007	G	24.5	2

•  通过on关联相同意义的字段

-- 3、join on 后的结果
select *
from db_order.sn_order_20210412 t1
join db_order.sn_orderitem_20210412 t2 on t2.order_id = t1.id 
where t1.id in ('A005','A019')
-- and t2.order_id in ('A005','A019');    /*相同意义的字段通过on关联后where只需要限制一个表里的条件就可以了*/

join on

订单号	订单金额	会员号	创建时间	订单状态	订单商品ID	订单号	商品ID	商品名称	商品价格	商品数量	手动计算实际订单金额
A005	1200.15	5	2017/1/1 13:23	2	AB004	A005	1001	A	0.15	1	0.15	1200.15
A005	1200.15	5	2017/1/1 13:23	2	AB005	A005	1004	D	200	6	1200
A019	99	12	2017/2/11 13:23	1	AB0016	A019	1006	F	50	1	50	99
A019	99	12	2017/2/11 13:23	1	AB0017	A019	1007	G	24.5	2	49

•  导向结果——取A005和A019的会员号，统计A005和A019的累计订单金额，订单数，商品数

-- 统计A005和A019的累计订单金额，订单数，商品数
select t1.id 
      ,t1.member_id 
      ,sum(t1.order_money) as money 
      ,count(t1.id) as order_num
      ,sum(t2.item_num) as item_num 
from db_order.sn_order_20210412 t1
join db_order.sn_orderitem_20210412 t2 on t2.order_id = t1.id 
where t1.id in ('A005','A019')
-- and t2.order_id in ('A005','A019')
group by t1.member_id ;

汇总统计

订单号	会员号	订单金额	订单数量	商品数量	实际手动计算订单金额
A005	5	2400.3	2	7	1200.15
A019	12	198	2	3	99

对比上表：发现订单金额查询出现逻辑错误，和实际计算的有偏差——没有语法错误，但是逻辑错误！

思考：

• 订单号是不是要去重
• 订单金额不能用原来的计算，即不能简单的用汇总函数sum(t1.order_money)直接进行汇总，这是对关联后的表进行数据统计，故会与实际结果不相符
• 是不是应该用另外一张表中的订单商品数量*商品价格计算订单金额—sum(t2.price*t2.item_num)

-- 5、修改逻辑错误
select t1.id 
      ,t1.member_id 
      ,sum(t2.price*t2.item_num) as money        /*修改逻辑错误*/
      ,count(distinct t1.id) as order_num        /*去重*/
      ,sum(t2.item_num) as item_num 
from db_order.sn_order_20210412 t1
join db_order.sn_orderitem_20210412 t2 on t2.order_id = t1.id 
where t1.id in ('A005','A019')
group by t1.member_id ;

修改逻辑错误后的结果

订单号	会员号	订单金额	订单数量	商品数量
A005	5	1200.15	1	7
A019	12	99	1	3

总结一些注意事项：

1. 如果没有on，只有join：把订单表中的每一行，都与订单明细表中每一行匹配
2. 如果加上on，表示在关联的时候只取能够匹配上的，也就是相同的记录；如果不相同的记录，就会不生成;【把不匹配的行不显示】
3. where作用：是把where满足条件的记录先筛选出来，再与另一张关联；如果不加where过滤条件，如果都匹配上，也相当于生成一个M*N行的相似数据量数据；