【MySQL】LEFT JOIN 踩坑

emdzz發表於2024-06-07
MySql

一、問題發現：

主查詢功能發現兩條一樣的記錄，但是審批狀態不一樣，一個已透過，一個待稽核

主表付款表：

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)

　　

　　

相關文章