背景

• 有三張表ltd1 、ltd0051和、ltd0011
• ltd1作為主表，左關聯 ltd0051和ltd0011
• 如果ltd0051有兩條重復數據、td0011有兩條重復數據，左關聯之后就會得到4條，同時ltd0051和ltd0011這兩條數據都是正確，基于主鍵我們需要將兩個相同主鍵的數據合并成一條

ltd0051和ltd0011 中是存在重復數據的，
ltd0051重復數據的判定規則：planid和batchno,MatCode相同的判定為重復數據
ltd0011重復數據的判定規則：planid和batchno，MatCode相同的判定為重復數據

第一步：實現需求

使用了兩個子查詢，對重復數據進行加和，得到兩個新表

SELECTltd1.plan_id_out,ltd1.batchno_out,ltd1.lot_id_out,ltd1.material_code_out,ltd1.material_name_out,ltd1.equip_id_out,ltd1.pro_date_out,ltd1.shift_id_out,ltd1.weight_out,ltd1.state_out,CASE WHEN t11.MATCODE IS NOT NULL THEN t11.MATCODE ELSE t51.MATCODE END AS material_code_in,CASE WHEN t11.MATNAME IS NOT NULL THEN t11.MATNAME ELSE t51.MATNAME END AS material_name_in,CASE WHEN t11.SWeight IS NOT NULL THEN t11.SWeight ELSE t51.ActWT END AS weight_in,t11.lotid AS lot_id_in,ltin.orderno AS plan_id_in,ltin.JDAT AS pro_date_in,ltin.JSHT AS shift_id_in,ltin.JMCH AS equip_id_in
FROM(SELECTORDERNO AS plan_id_out,LOTID AS lot_id_out,JDAT AS pro_date_out,CAST(JSHT AS INT) AS shift_id_out,JMCH AS equip_id_out,ITNBR AS material_code_out,ITDSC AS material_name_out,'' AS unit_cost_out,jwt AS weight_out,'' AS cost_out,CAST(VALUE AS INT) AS batchno_out,CASE WHEN STATE IN (4, 5) THEN '空走' ELSE '非空走' END AS state_outFROMPLMES.dbo.LTD0001CROSS APPLY STRING_SPLIT(CNUMNEW, ',')WHEREDIV <> 'XL') LTD1LEFT JOIN (SELECTplanid,batchno,MATCODE,SUM(ActWT) AS ActWT,MAX(MatName) AS MatNameFROMltd0051GROUP BYplanid, batchno, MATCODE) t51 ON LTD1.plan_id_out = t51.planid AND ltd1.batchno_out = t51.batchnoLEFT JOIN (SELECTplanid,batchno,MATCODE,SUM(CAST(SWeight AS DECIMAL(20, 10))) AS SWeight,MAX(MatName) AS MatName,lotidFROMltd0011GROUP BYplanid, batchno, MATCODE, lotid) t11 ON LTD1.plan_id_out = t11.planid AND ltd1.batchno_out = t11.batchno AND t51.MATCODE = t11.MATCODELEFT JOIN ltd0001 ltin ON t11.LOTID = ltin.lotid
WHERELTD1.pro_date_out = '2024-05-20'AND LTD1.shift_id_out = 1

優化查詢效率

• 查詢出來了，但是很慢

1. 索引優化：確保在 ltd0051 和 ltd0011 表的 planid、batchno 和 MATCODE 列上有適當的索引。但是這不是我們自己的表，無法實現。

2. 減少數據量：在子查詢中添加過濾條件，減少需要處理的數據量。但是，我們是根據主表ltd1作為篩選條件的，無法對子表進行條件查詢

3. CTE (Common Table Expressions)：使用 WITH 語句創建兩個 CTE (t51_agg 和 t11_agg) 來存儲聚合后的數據。

-- 取 產出數據
WITH LTD1 AS (SELECTORDERNO AS plan_id_out,LOTID AS lot_id_out,JDAT AS pro_date_out,CAST ( JSHT AS INT ) AS shift_id_out,JMCH AS equip_id_out,ITNBR AS material_code_out,ITDSC AS material_name_out,'' AS unit_cost_out,jwt AS weight_out,'' AS cost_out,CAST ( VALUE AS INT ) AS batchno_out,CASEWHEN STATE IN ( 4, 5 ) THEN'空走' ELSE '非空走' END AS state_out FROMPLMES.dbo.LTD0001 CROSS APPLY STRING_SPLIT ( CNUMNEW, ',' ) WHEREDIV <> 'XL' AND jdat = '2024-05-20' AND jsht = 1 ),
-- 關聯得到 稱重數據t51_agg AS (SELECTplanid,batchno,MATCODE,SUM ( ActWT ) AS ActWT,MAX ( MatName ) AS MatName FROMltd0051 GROUP BYplanid,batchno,MATCODE ),
-- 關聯得到 追溯數據t11_agg AS (SELECTplanid,batchno,MATCODE,SUM ( CAST ( SWeight AS DECIMAL ( 20, 10 ) ) ) AS SWeight,MAX ( MatName ) AS MatName,lotid FROMltd0011 GROUP BYplanid,batchno,MATCODE,lotid ) SELECTltd1.plan_id_out,ltd1.batchno_out,ltd1.lot_id_out,ltd1.material_code_out,ltd1.material_name_out,ltd1.equip_id_out,ltd1.pro_date_out,ltd1.shift_id_out,ltd1.weight_out,ltd1.state_out,
CASEWHEN t11.MATCODE IS NOT NULL THENt11.MATCODE ELSE t51.MATCODE END AS material_code_in,
CASEWHEN t11.MATNAME IS NOT NULL THENt11.MATNAME ELSE t51.MATNAME END AS material_name_in,
CASEWHEN t11.SWeight IS NOT NULL THENt11.SWeight ELSE t51.ActWT END AS weight_in,t11.lotid AS lot_id_in,ltin.orderno AS plan_id_in,ltin.JDAT AS pro_date_in,ltin.JSHT AS shift_id_in,ltin.JMCH AS equip_id_in 
FROMLTD1LEFT JOIN t51_agg t51 ON LTD1.plan_id_out = t51.planid AND ltd1.batchno_out = t51.batchnoLEFT JOIN t11_agg t11 ON LTD1.plan_id_out = t11.planid AND ltd1.batchno_out = t11.batchno AND t51.MATCODE = t11.MATCODELEFT JOIN ltd0001 ltin ON t11.LOTID = ltin.lotid;