[Sqlite]合併兩表不重複ID並計算總個數

一個產品在工程階段中有所謂的材料表（BOM），列舉出要組成此產品的所有零件、取得來源、數量...等等製造資訊；在材料運送任務中，為了節省運費或省下包裝成本，或許會將若干個產品包裝成一包寄出，此時需要將材料表合併，並統計個別零件的數量，作為運送資訊

產品間通常會有共通零件，比方螺絲，因此合併後的材料表材料項目，是可能少於原始個別材料表材料項目的加總，比如說產品1、產品2個別有50種零件，合併後的材料表只會小於等於100種零件．



若要求要以Sqlite實作，至此可以來定義問題模式：

給定兩張結構相同的表，其中二欄是PART_ID與數量，如何羅列此二表涵蓋的所有PART_ID，與加總數量？例如表1、表2分別如下，PART_ID：4只在表1有出現，數量（QTN）為1，PART_ID：2在兩張表都有出現，數量總數為6；我們需要取得兩張表的ID聯集，並以ID為索引，取得表1表2的對應數量後相加

表1

表2

--------------------------------------

第一步：取不重複的PART_ID，也就是PART_ID的聯集

聯集實際上是由3個集合組成：B-A差集、A-B差集、AB聯集，實作上需要先運用Join指令，合併表1表2，再透過on條件與選擇的欄位來控制取得差集或是交集，比方說若ON條件是兩者PART_ID相同的部分，則會取出交集；反之取出差集，選擇的欄位若是A.PART_ID則會取出A-B差集，反之會取出B-A差集

接著再利用Union指令將三個集合合併；將結果用一個試圖（View）呈現

%start
select B.PART_ID from (product_1 A join product_2 B on A.PART_ID<>B.PART_ID)
union
select A.PART_ID from (product_1 A join product_2 B on A.PART_ID=B.PART_ID)
union
select A.PART_ID from (product_1 A join product_2 B on A.PART_ID<>B.PART_ID)
%end

表1表2的PART_ID聯集

----------------------------

第二步：以第一步取得的PART_ID聯集表（P1ANDP2）為索引，將表1表2的對應數量用同一張表呈現，分別呈現在欄位1（QTN1）與欄位2（QTN2）；因為某些PART_ID在個別的原始表中並沒用到，所以可能會出現NULL的情況：

%start
select PART_ID,
(select QTN from PRODUCT_1 B where B.PART_ID=A.PART_ID) as QTN1 ,
(select QTN from PRODUCT_2 C where C.PART_ID=A.PART_ID) as QTN2
from P1ANDP2 A
%end

表1表2 PART_ID聯集並帶個別數量

----------------------------

第三步：將第二步的兩個數量欄位加總，需要注意的是，在Sqlite中數字與NULL相加後會是NULL，所以我們需要將NULL欄位轉化成數字0後再相加，可以運用Case...When..Else..End做類似IF-ELSE的轉換：遇到NULL轉換成0，非NULL保持原始值

%start
select A.PART_ID,
CASE
WHEN A.QTN1 IS NULL
THEN 0
ELSE A.QTN1
END
+
CASE
WHEN A.QTN2 IS NULL
THEN 0
ELSE A.QTN2
END
from P1ANDP2_SUM A
%end

統整PART_ ID與加總

打完收工，Sqlite經驗值+87