最近學習SQL遇到一個問題
先看第一段語法:
SELECT DISTINCT `machine`,SUM(A3.weight) FROM `work_line_product` A1 LEFT JOIN `production_managenment_assignment_process` A2 ON A1.`production_managenment_id` = A2.`production_managenment_id` AND A1.`production_sequence` = A2.`sequence`-1 LEFT JOIN `production_managenment` A3 ON A3.`production_managenment_id` = A1.`production_managenment_id` LEFT JOIN `customer_order` A4 ON A3.`order_id` = A4.`order_id` AND A3.`order_id_item` = A4.`order_id_item` WHERE A1.`work_status` = '已完成' AND A2.`production_process_basic` = '伸線' GROUP BY `machine`
這是執行結果:
再看第二段語法:
SELECT `machine`,SUM(A3.weight) FROM `work_line_product` A1 LEFT JOIN `production_managenment_assignment_process` A2 ON A1.`production_managenment_id` = A2.`production_managenment_id` AND A1.`production_sequence` = A2.`sequence` LEFT JOIN `production_managenment` A3 ON A3.`production_managenment_id` = A1.`production_managenment_id` LEFT JOIN `customer_order` A4 ON A3.`order_id` = A4.`order_id` AND A3.`order_id_item` = A4.`order_id_item`
WHERE A1.`work_status` = '施工中' AND A2.`production_process_basic` = '伸線'
這是執行結果:
我研究很久只能做出這樣，請看語法跟結果:
SELECT DISTINCT `machine`,SUM(A3.weight),(SELECT SUM(A3.weight) FROM `work_line_product` A1 LEFT JOIN `production_managenment_assignment_process` A2 ON A1.`production_managenment_id` = A2.`production_managenment_id` AND A1.`production_sequence` = A2.`sequence` LEFT JOIN `production_managenment` A3 ON A3.`production_managenment_id` = A1.`production_managenment_id` LEFT JOIN `customer_order` A4 ON A3.`order_id` = A4.`order_id` AND A3.`order_id_item` = A4.`order_id_item` WHERE A1.`work_status` = '施工中' AND A2.`production_process_basic` = '伸線')FROM `work_line_product` A1 LEFT JOIN `production_managenment_assignment_process` A2 ON A1.`production_managenment_id` = A2.`production_managenment_id` AND A1.`production_sequence` = A2.`sequence`-1 LEFT JOIN `production_managenment` A3 ON A3.`production_managenment_id` = A1.`production_managenment_id` LEFT JOIN `customer_order` A4 ON A3.`order_id` = A4.`order_id` AND A3.`order_id_item` = A4.`order_id_item` WHERE A1.`work_status` = '已完成' AND A2.`production_process_basic` = '伸線' GROUP BY `machine`
下圖是希望做到的結果：
這問題困擾很久，試過蠻多方法，子查詢、UNION都試過還是無解
還是不知道怎麼解決，求SQL大神寫個範例指導
已邀請的邦友 {{ invite_list.length }}/5
SELECT *
FROM (
第一段 SQL
) AS X
LEFT JOIN (
第二段 SQL
) AS Y ON Y.machine=X.machine
WHERE 1=1
ORDER BY X.machine
憑直覺寫的,試試看
以下作廢,因為你第一段跟第二段有一個地方 join 條件不一樣
第一段(A1.production_sequence = A2.sequence-1)
第二段(A1.production_sequence = A2.sequence)
要 join條件都一樣才可以這樣寫
SELECT `machine`,
SUM(case when A1.`work_status` = '已完成' AND A2.`production_process_basic` = '伸線' then A3.weight else 0 end) ,
SUM(case when A1.`work_status` = '施工中' AND A2.`production_process_basic` = '伸線' then A3.weight else 0 end)
FROM `work_line_product` A1 LEFT JOIN `production_managenment_assignment_process` A2
ON A1.`production_managenment_id` = A2.`production_managenment_id` AND A1.`production_sequence` = A2.`sequence`-1
LEFT JOIN `production_managenment` A3 ON A3.`production_managenment_id` = A1.`production_managenment_id`
LEFT JOIN `customer_order` A4 ON A3.`order_id` = A4.`order_id` AND A3.`order_id_item` = A4.`order_id_item`
WHERE A1.`work_status` in( '已完成','施工中') AND A2.`production_process_basic` = '伸線'
GROUP BY `machine
iThome鐵人賽
看影片追技術