【MySQL】LEFT JOIN 踩坑

 

一、问题发现：

主查询功能发现两条一样的记录，但是审批状态不一样，一个已通过，一个待审核

 

主表付款表：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `pur_or_payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统编码 初始值为“1”',
  `pa_code` varchar(32) NOT NULL COMMENT '付款编号',
  `pa_serv_ident` varchar(12) NOT NULL DEFAULT 'SN070201' COMMENT '付款业务走向',
  `sys_ar_cu_id` int(11) NOT NULL COMMENT '订单供应商',
  `pur_or_pp_id` varchar(255) NOT NULL COMMENT '条款id (多选)',
  `pa_amount` decimal(20,10) NOT NULL COMMENT '付款金额',
  `pa_state` char(1) NOT NULL COMMENT '付款状态 付款中，已付款',
  `pa_date` datetime DEFAULT NULL COMMENT '付款日期',
  `creator` varchar(32) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单付款表';

 

主表关联了两张副表，两张副表也是张业务表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
CREATE TABLE `fin_ex_apply` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统编码 主键，初始值为“10000001”',
  `ap_code` varchar(14) NOT NULL COMMENT '单据编码',
  `sys_ar_co_id` int(11) NOT NULL COMMENT '所属公司 引用表<公司档案> ',
  `sys_ar_de_id` int(11) NOT NULL COMMENT '申请部门 引用表<部门档案> ',
  `ap_serv_id` int(11) DEFAULT NULL COMMENT '其它业务id',
  `ap_serv_ident` varchar(32) DEFAULT NULL COMMENT '业务来源标识',
  `ap_date` datetime NOT NULL COMMENT '申请日期',
  `ap_proposer` varchar(32) NOT NULL COMMENT '申请人 引用表<员工档案>',
  `ap_ro_type` varchar(32) NOT NULL COMMENT '往来对象类型 related object',
  `ap_re_obj` int(11) NOT NULL COMMENT '往来对象 <员工档案><客商档案>',
  `ap_remark` text COMMENT '申请备注',
  `ap_de_amount` decimal(11,2) NOT NULL COMMENT '冲账金额 deduct amount',
  `ap_pa_amount` decimal(11,2) NOT NULL COMMENT '付款金额 payment amount',
  `ap_to_amount` decimal(11,2) NOT NULL COMMENT '合计金额 total amount',
  `ap_sett_meth` varchar(32) DEFAULT NULL COMMENT '引用内置<结算方式> settlement method',
  `ap_pa_remark` varchar(255) DEFAULT NULL COMMENT '付款备注 payment remark',
  `ap_re_payee` varchar(32) NOT NULL COMMENT '收款人 payee',
  `ap_re_account` varchar(32) NOT NULL COMMENT '收款账号 receive account',
  `ap_re_ba_name` varchar(64) NOT NULL COMMENT '收款银行 recevie bank',
  `ap_re_subbranch` varchar(64) DEFAULT NULL COMMENT '收款支行',
  `ap_re_ba_locus` varchar(128) NOT NULL COMMENT '开户行所在地',
  `ap_appr_state` char(1) NOT NULL COMMENT '审核状态 0审核中 1已审核 2不通过',
  `ap_appr_date` datetime DEFAULT NULL COMMENT '审核日期',
  `creator` varchar(32) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `updator` varchar(32) NOT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `status` char(1) NOT NULL COMMENT '记录状态 1正常，0删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10000069 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='报销申请表';
 
CREATE TABLE `fin_sp_apply` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统编码 主键，初始值为“10000001”',
  `ap_code` varchar(14) NOT NULL COMMENT '单据编码',
  `sys_ar_co_id` int(11) NOT NULL COMMENT '所属公司 引用表<公司档案> ',
  `sys_ar_de_id` int(11) NOT NULL COMMENT '申请部门 引用表<部门档案> ',
  `ap_serv_id` int(11) DEFAULT NULL COMMENT '其它业务id',
  `ap_serv_ident` varchar(32) DEFAULT NULL COMMENT '业务来源标识',
  `ap_date` datetime NOT NULL COMMENT '申请日期',
  `ap_proposer` varchar(32) NOT NULL COMMENT '申请人 引用表<员工档案>',
  `ap_ro_type` varchar(32) NOT NULL COMMENT '往来对象类型 选择：单位/员工',
  `ap_re_obj` int(11) NOT NULL COMMENT '往来对象 引用表<客商档案><员工档案>',
  `ap_remark` text COMMENT '申请备注',
  `ap_to_amount` decimal(11,2) NOT NULL COMMENT '合计金额 total',
  `ap_sett_meth` varchar(32) NOT NULL COMMENT '付款结算方式 引用内置<付款结算方式> settlement method',
  `ap_pa_remark` varchar(255) NOT NULL COMMENT '付款备注',
  `ap_re_payee` varchar(32) NOT NULL COMMENT '收款人',
  `ap_re_account` varchar(32) NOT NULL COMMENT '收款账号',
  `ap_re_bank` varchar(64) NOT NULL COMMENT '收款银行',
  `ap_re_subbranch` varchar(64) DEFAULT NULL COMMENT '收款支行',
  `ap_re_ba_locus` varchar(128) NOT NULL COMMENT '开户行所在地',
  `ap_appr_state` char(1) DEFAULT NULL COMMENT '审核状态 0审核中 1已审核 2不通过',
  `ap_appr_date` datetime DEFAULT NULL COMMENT '审核日期',
  `creator` varchar(32) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `updator` varchar(32) NOT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `status` char(1) NOT NULL COMMENT '记录状态 1正常，0删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `ap_code` (`ap_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10000032 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用款申请信息表';

　　

二、问题排查

两个重复记录的明细和表单更新访问都是报错，错误日志显示，查询到了多条记录

 

错误定位到查询SQL上：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT
 pa.*,
 cu.cu_name AS cuName,
 cu.cu_code AS cuCode,
 us.us_name AS creatorName,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.ap_appr_state
  WHEN 'SN070101' THEN ex.ap_appr_state
  ELSE ex.ap_appr_state
 END AS apApprState,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.id
  WHEN 'SN070101' THEN ex.id
  ELSE sp.id 
 END AS paServId,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN '用款申请' 
  WHEN 'SN070101' THEN '费用报销' 
  ELSE '用款申请' 
 END AS paServIdentName 
FROM
 pur_or_payment AS pa
 LEFT JOIN fin_sp_apply AS sp ON pa.id = sp.ap_serv_id AND pa.pa_serv_ident = 'SN070201'
 LEFT JOIN fin_ex_apply AS ex ON pa.id = ex.ap_serv_id AND pa.pa_serv_ident = 'SN070101'
 LEFT JOIN sys_ar_customer AS cu ON cu.id = pa.sys_ar_cu_id
 LEFT JOIN sys_pr_user AS us ON us.us_username = pa.creator

　　

定位到问题记录上的SQL结果是这样：

 发现单号BX231100193也被加入进来了

所以定位到SQL条件就在这两段：

?
1
2
LEFT JOIN fin_sp_apply AS sp ON pa.id = sp.ap_serv_id AND pa.pa_serv_ident = 'SN070201'
LEFT JOIN fin_ex_apply AS ex ON pa.id = ex.ap_serv_id AND pa.pa_serv_ident = 'SN070101' 　　

第二个条件不生效，BX231100193是销售合同的，要筛选采购订单的，显然条件未生效

 

三、问题排查

同事认为第二个条件还是在以主表的记录内进行筛选，并没有涉及到副表的筛选

才导致重复记录的出现，所以解决办法是转换成副表的筛选条件

?
1
2
3
4
5
6
7
LEFT JOIN fin_sp_apply AS sp ON pa.id = sp.ap_serv_id AND pa.pa_serv_ident = 'SN070201'
 LEFT JOIN fin_ex_apply AS ex ON pa.id = ex.ap_serv_id AND pa.pa_serv_ident = 'SN070101' 　　
 
更改为
 
LEFT JOIN fin_sp_apply AS sp ON pa.id = sp.ap_serv_id AND sp.ap_serv_ident = 'SN050104'
LEFT JOIN fin_ex_apply AS ex ON pa.id = ex.ap_serv_id AND ex.ap_serv_ident = 'SN050104'

　　

查询后发现，BX231100193单号已经过滤，结果正确

 

四、本地环境复盘

担心只是巧合，在本地开发环境进行复现

以费用报销的一张单据为例：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT
 pa.pa_code,
 pa.pa_serv_ident,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.ap_code
  WHEN 'SN070101' THEN ex.ap_code
  ELSE ''
    END AS apCode,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.ap_appr_state
  WHEN 'SN070101' THEN ex.ap_appr_state
  ELSE ex.ap_appr_state
 END AS apApprState,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.id
  WHEN 'SN070101' THEN ex.id
  ELSE sp.id 
 END AS paServId,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN '用款申请' 
  WHEN 'SN070101' THEN '费用报销' 
  ELSE '用款申请' 
 END AS paServIdentName 
FROM
 pur_or_payment AS pa
 LEFT JOIN fin_sp_apply AS sp ON pa.id = sp.ap_serv_id AND pa.pa_serv_ident = 'SN070201'
 LEFT JOIN fin_ex_apply AS ex ON pa.id = ex.ap_serv_id AND pa.pa_serv_ident = 'SN070101'
 LEFT JOIN sys_ar_customer AS cu ON cu.id = pa.sys_ar_cu_id
 LEFT JOIN sys_pr_user AS us ON us.us_username = pa.creator
 WHERE pa_code = 'DF24030001'
 ORDER BY pa_code DESC;
  
+------------+---------------+-------------+-------------+----------+-----------------+
| pa_code    | pa_serv_ident | apCode      | apApprState | paServId | paServIdentName |
+------------+---------------+-------------+-------------+----------+-----------------+
| DF24030001 | SN070101      | BX240300001 | 3           | 10000063 | 费用报销         |
+------------+---------------+-------------+-------------+----------+-----------------+
1 rows in set (0.08 sec)

　　

单据编号为 BX240300001，根据这张单据我们创建一条”重复记录“

单据编号为 BX340300001，但是业务类别为”销售合同“

?
1
INSERT INTO `fin_ex_apply`  VALUES (NULL, 'BX340300001', 1003, 10000021, 8, 'SN030601', '2024-03-19 10:24:03', ....); -- 后面信息省略

　　

再次查询，问题复现，出现两条相同记录：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> SELECT
 pa.pa_code,
 pa.pa_serv_ident,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.ap_code
  WHEN 'SN070101' THEN ex.ap_code
  ELSE ''
    END AS apCode,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.ap_appr_state
  WHEN 'SN070101' THEN ex.ap_appr_state
  ELSE ex.ap_appr_state
 END AS apApprState,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.id
  WHEN 'SN070101' THEN ex.id
  ELSE sp.id 
 END AS paServId,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN '用款申请' 
  WHEN 'SN070101' THEN '费用报销' 
  ELSE '用款申请' 
 END AS paServIdentName 
FROM
 pur_or_payment AS pa
 LEFT JOIN fin_sp_apply AS sp ON pa.id = sp.ap_serv_id AND pa.pa_serv_ident = 'SN070201'
 LEFT JOIN fin_ex_apply AS ex ON pa.id = ex.ap_serv_id AND pa.pa_serv_ident = 'SN070101'
 LEFT JOIN sys_ar_customer AS cu ON cu.id = pa.sys_ar_cu_id
 LEFT JOIN sys_pr_user AS us ON us.us_username = pa.creator
 WHERE pa_code = 'DF24030001'
 ORDER BY pa_code DESC;
  
+------------+---------------+-------------+-------------+----------+-----------------+
| pa_code    | pa_serv_ident | apCode      | apApprState | paServId | paServIdentName |
+------------+---------------+-------------+-------------+----------+-----------------+
| DF24030001 | SN070101      | BX240300001 | 3           | 10000063 | 费用报销        |
| DF24030001 | SN070101      | BX340300001 | 3           | 10000068 | 费用报销        |
+------------+---------------+-------------+-------------+----------+-----------------+
2 rows in set (0.09 sec)

　　

改用修正后的SQL：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> SELECT
 pa.pa_code,
 pa.pa_serv_ident,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.ap_code
  WHEN 'SN070101' THEN ex.ap_code
  ELSE ''
    END AS apCode,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.ap_appr_state
  WHEN 'SN070101' THEN ex.ap_appr_state
  ELSE ex.ap_appr_state
 END AS apApprState,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN sp.id
  WHEN 'SN070101' THEN ex.id
  ELSE sp.id 
 END AS paServId,
 CASE pa.pa_serv_ident 
  WHEN 'SN070201' THEN '用款申请' 
  WHEN 'SN070101' THEN '费用报销' 
  ELSE '用款申请' 
 END AS paServIdentName 
FROM
 pur_or_payment AS pa
 LEFT JOIN fin_sp_apply AS sp ON pa.id = sp.ap_serv_id AND sp.ap_serv_ident = 'SN050104'
 LEFT JOIN fin_ex_apply AS ex ON pa.id = ex.ap_serv_id AND ex.ap_serv_ident = 'SN050104'
 LEFT JOIN sys_ar_customer AS cu ON cu.id = pa.sys_ar_cu_id
 LEFT JOIN sys_pr_user AS us ON us.us_username = pa.creator
 WHERE pa_code = 'DF24030001'
 ORDER BY pa_code DESC;
+------------+---------------+-------------+-------------+----------+-----------------+
| pa_code    | pa_serv_ident | apCode      | apApprState | paServId | paServIdentName |
+------------+---------------+-------------+-------------+----------+-----------------+
| DF24030001 | SN070101      | BX240300001 | 3           | 10000063 | 费用报销        |
+------------+---------------+-------------+-------------+----------+-----------------+
1 row in set (0.04 sec)

　　

　　

 

posted @   emdzz  阅读(44)  评论(0)  编辑  收藏  举报
（评论功能已被禁用）
相关博文：
· 【MySQL】 线上补数据SQL
· 【MySQL】查询是否连续5次失败
· MySQL表连接及其优化（转）
· ShowMeAI-人工智能工具笔记-八-
· SQL中 LEFT JOIN 左表合并去重实用技巧
阅读排行：
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· 单线程的Redis速度为什么快？
· 展开说说关于C#中ORM框架的用法！
· Pantheons：用 TypeScript 打造主流大模型对话的一站式集成库
· SQL Server 2025 AI相关能力初探
历史上的今天：
2022-06-07 【SQL】 牛客网SQL训练Part3 较难难度
2022-06-07 【Java】 Void 类型
2022-06-07 【MySQL】查询是否连续5次失败
2020-06-07 【Java】【设计模式 Design Pattern】迭代器模式 Iterator/Cursor
2020-06-07 【Java】【设计模式 Design Pattern】命令模式 Command
点击右上角即可分享
微信分享提示