iT邦幫忙

0

MySQL有關Select Left Join語法的問題

Nai 2021-02-04 17:23:30518 瀏覽

最近使用SQL遇到問題

先看第一段語法:

SELECT invoice_details_new.invoice_number,invoicing_system_new.invoice_date,invoice_details_new.quantity,invoice_details_new.price,invoice_details_new.money FROM Invoicing_system_new LEFT JOIN invoice_details_new ON invoicing_system_new.invoice_number = invoice_details_new.invoice_number WHERE invoice_date LIKE '2021-01%' ORDER BY invoicing_system_new.invoice_number

這是第一段語法執行結果:https://ithelp.ithome.com.tw/upload/images/20210204/20110597zrvh1fjKkI.png

再看第二段語法:

SELECT DISTINCT invoice_details_new.invoice_number,invoicing_system_new.total_amount,invoicing_system_new.allowance,invoicing_system_new.total FROM Invoicing_system_new LEFT JOIN invoice_details_new ON invoicing_system_new.invoice_number = invoice_details_new.invoice_number WHERE invoice_date LIKE '2021-01%' ORDER BY invoicing_system_new.invoice_number

這是第二段語法執行結果:https://ithelp.ithome.com.tw/upload/images/20210204/201105978dVAAVdXDj.png

目前研究很久只能做出這樣:
https://ithelp.ithome.com.tw/upload/images/20210204/2011059764DtY7IMfP.png

下圖是希望做到的結果:
https://ithelp.ithome.com.tw/upload/images/20210204/20110597M5DEFPOe2i.png

這問題困擾很久,感覺跟LEFT JOIN有關
一直試但是都沒有結果,求SQL大神寫個範例指導

石頭 iT邦研究生 2 級 ‧ 2021-02-04 18:13:51 檢舉
你在找尋 Row_Number() with window function
你mysql 版本是多少? 如果是8.0以上才有上面語法可以用
低於8.0 寫法會比較麻煩 要自己做 Row_Number() with window function
石頭 iT邦研究生 2 級 ‧ 2021-02-04 18:15:58 檢舉
https://stackoverflow.com/questions/1895110/row-number-in-mysql
石頭 iT邦研究生 2 級 ‧ 2021-02-04 18:16:39 檢舉
得到row number 後在使用CASE WHEN 處理結果集

尚未有邦友回答

立即登入回答