請教各位大神以下oracle查詢若我只想顯示發票編號欄位只有一筆語法該如何修正
join有問題
select dh.item_code 產品編號,dh.c_short_de 產品名稱,dd.inv_num 發票號碼,dd.shop_code 購買店編,sp.shop_cname 購買店名,
dd.member_code 會員編號, ms.Chinese_name 中文姓名,ms.Phone_Day 聯絡電話,ms.e_mail,to_char(dd.inv_date,'YYYY/MM/DD') 購買日 from dinv_hdr dd
left join dinv_dtl dh ON (dd.inv_num = dh.inv_num)
left join shop sp on (sp.shop_code = dh.shop_code)
left join membership ms on (ms.shop_code = dh.shop_code)
WHERE dh.item_code = ('0640046230') and dd.shop_code = ('9110133')
and to_char(dd.inv_date,'YYYY/MM/DD') >='2022/07/01' and to_char(dd.inv_date,'YYYY/MM/DD') <= '2022/07/31'
order by dd.inv_num , dd.member_code
--order by dd.item_code,dd.inv_num
![https://ithelp.ithome.com.tw/upload/images/20220802/20148637gPxqOA6369.png](https://ithelp.ithome.com.tw/upload/images/20220802/20148637gPxqOA6369.png)
GROUP BY dh.item_code 產品編號,dh.c_short_de 產品名稱,dd.inv_num 發票號碼,dd.shop_code 購買店編,sp.shop_cname 購買店名,
dd.member_code 會員編號, ms.Chinese_name 中文姓名,ms.Phone_Day 聯絡電話,ms.e_mail,inv_date
ORDER BY dd.inv_num , dd.member_code
?
感謝回復結果還是一樣
select dh.item_code 產品編號,dh.c_short_de 產品名稱,dd.inv_num 發票號碼,dd.shop_code 購買店編,sp.shop_cname 購買店名,
dd.member_code 會員編號, ms.Chinese_name 中文姓名,ms.Phone_Day 聯絡電話,ms.e_mail,to_char(dd.inv_date,'YYYY/MM/DD') 購買日 from dinv_hdr dd
left join dinv_dtl dh ON (dd.inv_num = dh.inv_num)
left join shop sp on (sp.shop_code = dh.shop_code)
left join membership ms on (ms.shop_code = dh.shop_code)
WHERE dh.item_code = ('0640046230') and dd.shop_code = ('9110133')
and to_char(dd.inv_date,'YYYY/MM/DD') >='2022/07/01' and to_char(dd.inv_date,'YYYY/MM/DD') <= '2022/07/31'
GROUP BY dh.item_code ,dh.c_short_de ,dd.inv_num ,dd.shop_code ,sp.shop_cname ,
dd.member_code , ms.Chinese_name ,ms.Phone_Day ,ms.e_mail,inv_date
ORDER BY dd.inv_num , dd.member_code
麻煩再詳細敘述需求
(不清楚你是要查詢結果只顯示一筆,還是要有對應的發票號碼)
一筆發票只有一筆銷售紀錄
我不知為何join後會成為多筆銷售紀錄
我查過資料庫一筆發票號碼就一筆
沒有第二筆
left join membership ms on (ms.shop_code = dh.shop_code)
看起來是這行錯了
你可以把中間去掉像
select * from xxx
left join xxx
......
看一下資料的勾稽狀態
left join membership ms on (ms.shop_code = dh.shop_code)
會導致購買人勾稽到該店家所有的消費紀錄
on (ms.shop_code = dh.shop_code) and XXX
再加條件應該就可以了
membership是客戶資料主檔需join不然無客戶資料
我需撈出購買人資訊
select dh.item_code 產品編號
,dh.c_short_de 產品名稱
,dd.inv_num 發票號碼
,dd.shop_code 購買店編
,sp.shop_cname 購買店名
,dd.member_code 會員編號
, ms.Chinese_name 中文姓名
,ms.Phone_Day 聯絡電話
,ms.e_mail,to_char(dd.inv_date,'YYYY/MM/DD') 購買日 from dinv_hdr dd
left join dinv_dtl dh ON (dd.inv_num = dh.inv_num)
left join shop sp on (sp.shop_code = dh.shop_code)
left join membership ms on (ms.membercode_code = dd.member_code)
WHERE dh.item_code = ('0640046230')
and dd.shop_code = ('9110133')
and to_char(dd.inv_date,'YYYY/MM/DD') >='2022/07/01'
and to_char(dd.inv_date,'YYYY/MM/DD') <= '2022/07/31'
order by dd.inv_num , dd.member_code
盲寫的 大概吧
我前面誤會了 不用group by
哈!邏輯錯誤感謝您 讚