【MySQL】LEFT JOIN 踩坑
一、问题发现：
主查询功能发现两条一样的记录，但是审批状态不一样，一个已通过，一个待审核
主表付款表：
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='订单付款表';
主表关联了两张副表，两张副表也是张业务表
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上：
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条件就在这两段：
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是销售合同的，要筛选采购订单的，显然条件未生效
三、问题排查
同事认为第二个条件还是在以主表的记录内进行筛选，并没有涉及到副表的筛选
才导致重复记录的出现，所以解决办法是转换成副表的筛选条件
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单号已经过滤，结果正确
四、本地环境复盘
担心只是巧合，在本地开发环境进行复现
以费用报销的一张单据为例：
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，但是业务类别为”销售合同“
INSERT INTO `fin_ex_apply` VALUES (NULL, 'BX340300001', 1003, 10000021, 8, 'SN030601', '2024-03-19 10:24:03', ....); -- 后面信息省略
再次查询，问题复现，出现两条相同记录：
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：
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)
