首页 > 数据库 >【MySQL】LEFT JOIN 踩坑

【MySQL】LEFT JOIN 踩坑

时间:2024-06-07 09:57:33浏览次数:26  
标签:COMMENT serv JOIN ap pa MySQL NULL id LEFT

 

一、问题发现:

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

 

主表付款表:

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)

  

  

 

标签:COMMENT,serv,JOIN,ap,pa,MySQL,NULL,id,LEFT
From: https://www.cnblogs.com/mindzone/p/18236568

相关文章

  • 基于java ssm vue mysql志愿者招募网站(源码+lw+部署文档+讲解等)
    前言......
  • 基于java ssm vue mysql协同过滤算法的电影推荐系统(源码+lw+部署文档+讲解等)
    前言......
  • Springboot计算机毕业设计疫苗注射小程序【附源码】开题+论文+mysql+程序+部署
    本系统(程序+源码)带文档lw万字以上 文末可获取一份本项目的java源码和数据库参考。系统程序文件列表开题报告内容研究背景在全球疫苗接种工作日益重要的背景下,为了更有效地推进疫苗接种进程,提高接种效率和用户体验,疫苗注射小程序的开发显得尤为关键。随着移动互联网的普及......
  • 37.MySQL进阶知识
    MySQL进阶知识【一】试图1)概念视图就是通过查询得到一张虚拟表,然后保存下来,下次可以直接使用在计算机科学中,视图(View)是一种虚拟表,其内容是一个或多个基本表的查询结果。视图可以用于简化复杂查询、隐藏敏感数据、实现数据安全性和完整性约束等视图可以是虚拟的,也可......
  • 36.PyMySQL模块
    PyMySQL模块【一】简介DB-API:Python标准数据库规范为DB-API,DB-API定义了一系列必须的对象和数据库操作方式,以便为各种数据库系统和数据库访问程序提供一致的访问接口。PyMySQL:纯python实现的模块,可以与Python代码兼容衔接,也几乎兼容MySQL-python遵循Python数据库AP......
  • Springboot计算机毕业设计疫苗预约微信小程序【附源码】开题+论文+mysql+程序+部署
    本系统(程序+源码)带文档lw万字以上 文末可获取一份本项目的java源码和数据库参考。系统程序文件列表开题报告内容研究背景在数字化和智能化日益发展的今天,公众对于便捷、高效的健康服务需求日益增长。疫苗接种作为预防疾病的重要手段,其预约和接种流程的便捷性直接影响到公......
  • MySQL主从同步优化指南:架构、瓶颈与解决方案
    前言​在现代数据库架构中,MySQL主从同步是实现高可用性和负载均衡的关键技术。本文将深入探讨主从同步的架构、延迟原因以及优化策略,并提供专业的监控建议。MySQL主从同步架构​主从复制流程:从库生成两个线程,一个I/O线程,一个SQL线程;I/O线程去请求主库的bin......
  • MySQL三大日志
    总述:undolog日志是inndb存储引擎层生成的日志,实现了事务的原子性,主要用于事务回滚和MVCC。redolog日志是inndb存储引擎层生成的日志,实现了事务的持久性,主要用于掉电等故障恢复。binlog日志是Server层生成的日志,主要用于数据备份和主从复制。undolog回滚日志undolog是......
  • 如何在Slider中使用MouseLeftButtonDown事件
    MouseLeftButtonDown和MouseLeftButtonUp是两个经常同时使用的事件,最常用到的恐怕就是处理鼠标拖动,这种时候需要在Down事件中CaptureMouse,而在Up事件中Release。但是我在对一个继承Slider的控件这么做的时候却发现MouseLeftButtonUp事件可以正常触发,而MouseLeftButtonDown怎么也......
  • 聊聊如何理解MySQL引擎--Innodb和MyISAM和Memory
    mysql的常见的引擎有许多比如,MYISAM、Innodb、Memory、MERGE。可以通过mysql>showengines;查看数据库提供的引擎:我们今天聊聊如何理解Innodb和MyISAM,Memory,这是面试时会问的问题。首先是Innodb:先上概念:Innodb:行级锁,提供了具有提交、回滚和崩溃回复能力的事务安全,支......