比如我现在有两张表:
①t_storage_order 代表产品入库订单
②t_approval 代表单据的审批数据
t_storage_order的字段有:
t_code t_storage_date t_storage_number t_creator t_created_datetime
t_approval的字段有:
t_approval_id t_order_code t_approval_status t_latest t_created_datetime
t_storage_order的t_code和t_approval的t_order_code是需要进行链接的
假如现在两张表的数据是这样子的:
t_storage_order 产品入库单表
t_code t_storage_date t_storage_number t_creator t_created_datetime
1 2023-09-27 12 zz 2023-10-17 15:27:41
2 2023-09-24 12 zz 2023-10-17 15:27:41
3 2023-09-23 14 zz 2023-10-17 15:27:41
4 2023-09-21 14 zz 2023-10-17 15:27:41
t_approval 审批数据表
t_approval_id t_order_code t_approval_status t_latest t_created_datetime
1 1 1 1 2023-10-17 17:10:10
2 1 0 0 2023-10-17 17:09:10
3 1 0 0 2023-10-17 17:08:10
4 2 1 1 2023-10-17 17:10:10
5 2 0 0 2023-10-17 17:09:10
6 3 1 1 2023-10-17 17:10:10
7 3 0 1 2023-10-17 17:09:10
8 3 0 0 2023-10-17 17:08:10
现在大致讲一下t_approval这张表里面的字段代表的意思。
t_approval的latest:
代表是否是最新状态;
-latest为1代表最新状态,就无法继续提交审批了
-latest为0代表不是最新状态,可以继续提交新的审批数据或者修改审批数据。 approval_status 的1代表同意,0代表撤回,2代表审核中,如果对应的入库单code没有在审批数据表里面,那就代表还未提交。(例如在t_approval表里面没有存在storage_code是4的,所以此时如果要使用left join查找该单的审批数据的话,肯定就都是null。)如果是撤回可以多次提交。
例如t_order_code为1的数据行记录有3行,前面2行创建时间最新的都是0,代表原本一开始提交了,然后显示2审核中,然后又撤回,将approval_status更新为0,又提交了一次,又新增一条数据,approval_status又是2,又撤回,又更新approval_status为0,又提交,又新增一条approval_status是2,然后此时同意了,更新approval_status是1。
这个业务其实这里不做要求,关键是想拿出一个非常应景的例子给大家说存在的坑!
比如现在我要查询storage_order里面所有字段以及对应的审批状态。
那么我们的sql语句就是这样写:2刚刚在上面说了,为null代表未提交,未提交的状态是2
select tso.* , if(ta.approval_stataus is null , 2,ta.approval_status) as approval_status
from t_storage_order tso
left join t_approval ta
on tso.t_code = ta.order_code
这时候因为有些入库单可能对应多条审批数据,那么此时我们应该筛选latest为1的
那么此时如果我们直接
select tso.*,if(ta.t_approval_status is null , 2 ,ta.t_approval_status) as approval_status
from t_storage_order tso
left join t_approval ta
on tso.t_code = ta.t_order_code where ta.t_latest = true
大家想想对不对。思考一下,过滤掉latest = 1的,那么刚刚有一条t_code是4的它可是没提交过,它的右表t_approval的数据可是全是null的,那么这样会不会就把t_code为4的数据剔除掉了呢?大家可以思考一下。现在我们看看查出的数据是什么,结果如下:结果表明确实t_code为4的没有了。
那如果我们这样写呢?
select tso.*,if(ta.t_approval_status is null , 2 ,ta.t_approval_status) as approval_status
from t_storage_order tso
left join t_approval ta
on tso.t_code = ta.t_order_code and ta.t_latest = true
为什么会这样呢?我们分析一下
第一条sql如果我们不加where过滤的话,那么结果是这样的:(为了方便叙说,我们再查一下t_latest)
由于t_code是4的话得latest是null,所以where之后肯定就被筛选掉了。
我们看看第二条sql,第二条sql的话如果不加and也是照样和上面一样的结果:
这里的and,代表各自左连接之后的并且,不属于where,那么它在这里代表的意思是,左连接之后将连接之后的数据找出latest为true的,比如t_code为1的,左连接之后的数据行有3条,那么在这三条里面and并且出latest为true的,那么就会筛选出第三条。t_code为2的,就会筛选出二条。t_code为3的,就会筛选出第3条。t_code为4由于t_latest是null,所以要筛选出latest为1的话,因为null比较特殊,所以无论如何latest这时候筛选其他数字比如0,也照样会有,这里的null可以看作是一个任意值,所以无论如何都会被筛选出来。
总结:
左连接left...join需要过滤右表某个字段
一、如果用and的话是在每个左连接之后的基础上,对连接之后的数据行就进行了筛选,如果能筛选出的话就按照筛选条件筛选,如果不能筛选出的话,那么最终结果就是只会显示一行数据,左表的所有字段数据,右表的所有字段数据都是null,但是为什么下面的approval_status都是2呢,那是因为我查询字段的时候,写了一个if判断。假如下面我筛and并且出latest为100的。
二、但是如果是null的话,无论如何都会筛选出。
三、那么如果在最后用where的话,就是在所有结果集里面筛选出符合条件的记录,这时候就无论是什么值了,只要不满足就查不到,null也如此!