MySQL有關Select語法的問題

mysql sql

hnl1201 2019-12-25 14:49:02 ‧ 1578 瀏覽

分享至

最近學習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大神寫個範例指導

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2019-12-25 16:13:50

最佳解答

SELECT *
FROM (
第一段 SQL
) AS X
LEFT JOIN (
第二段 SQL
) AS Y ON Y.machine=X.machine
WHERE 1=1
ORDER BY X.machine

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

paicheng0111 iT邦大師 5 級 ‧ 2019-12-25 16:16:49 檢舉

為何要加上WHERE 1=1？

阿海 iT邦新手 2 級 ‧ 2019-12-25 16:36:32 檢舉

WHERE 1=1 可以看成true這樣理解?

動態SQL比較會用到
你這串應該是用不太到

ccutmis iT邦高手 2 級 ‧ 2019-12-25 17:18:52 檢舉

請Google "mysql 1=1 效率"

hnl1201 iT邦新手 5 級 ‧ 2019-12-25 17:25:01 檢舉

那如果說要把第二段的machine拿掉呢?
就變成是machine>第一段數字>第二段數字
因為拿掉第二段的machine下面就不能使用Y.machine=X.machine了

rogeryao iT邦超人 7 級 ‧ 2019-12-25 17:53:03 檢舉

SELECT *
改成你要顯示的欄位

hnl1201 iT邦新手 5 級 ‧ 2019-12-25 21:21:24 檢舉

已解決，謝謝

登入發表回應

twyes

iT邦新手 4 級 ‧ 2019-12-25 15:12:24

憑直覺寫的,試試看
以下作廢,因為你第一段跟第二段有一個地方 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