在Oracle SQLPLUS裡使用SQL指令的問題

lcw1960 2013-02-21 13:30:00 ‧ 4528 瀏覽

分享至

select ima01 as 料號,ima53 as 最近採購價,ima91 as 採購平均價,
ima531 as 市價,pmj05 as 幣別,pmj07 as 核價
from ima_file left outer join
(pmj_file a left outer join pmi_file b
on a.pmj01 = b.pmi01 and b.pmiconf = 'Y'
and a.pmj09 = (select max(pmj09) from pmj_file c,pmi_file d
where c.pmj01 = d.pmi01 and d.pmiconf = 'Y'
and a.pmj03 = c.pmj03))
on ima01 = a.pmj03 and (a.pmj07 != 0 or a.pmj07 is not null)
where imaacti = 'Y'
and ima01 like '02-SGM85-811';

以上是我要抓料號02-SGM85-811的最近採購價/採購平均單價/市價/核價單價(最新的)
的相關欄位,但是因為該料號有二筆核價單,但我卻只要抓最近的核價單價,結果卻抓出二筆,
之所以要用outer join,是因為料件基本資料檔已有最近採購價/採購平均價/市價,但User還要同時看到該料號核價單價,而核價單價存放在pmj_file,因此用以上的SQL,但就是沒辦法只抓出一筆(含最新核價),請網友幫忙謝謝.

tzunghua iT邦新手 4 級 ‧ 2013-02-22 22:50:30 檢舉

SELECT ima01 AS 料號,  
       ima53 AS 最近採購價,ima91 AS 採購平均價,  
       ima531 AS 市價,pmj05 AS 幣別,pmj07 AS 核價   
  FROM ima_file LEFT OUTER JOIN (pmj_file a LEFT OUTER JOIN pmi_file b   
                                 ON a.pmj01 = b.pmi01 AND b.pmicONf = 'Y'  
                                 AND a.pmj01 = (SELECT max(pmj01)   
                                                  FROM pmj_file c,  
                                                       pmi_file d  
                                                 WHERE c.pmj01 = d.pmi01   
                                                   AND d.pmicONf = 'Y'  
                                                   AND a.pmj03 = c.pmj03 
                                                   AND ROWNUM = 1
                                               )  
                                ) ON ima01 = a.pmj03   
                                  AND (a.pmj07 != 0 or a.pmj07 is not null)  
  WHERE imaacti = 'Y'

用這一段試試看

登入發表討論

直播研討會

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

直播中

1 個回答

tzunghua

iT邦新手 4 級 ‧ 2013-02-21 22:16:56

最佳解答

&lt;pre class="c" name="code">
SELECT ima01 AS 料號,
       ima53 AS 最近採購價,ima91 AS 採購平均價,
       ima531 AS 市價,pmj05 AS 幣別,pmj07 AS 核價 
  FROM ima_file LEFT OUTER JOIN (pmj_file a LEFT OUTER JOIN pmi_file b 
                                 ON a.pmj01 = b.pmi01 AND b.pmicONf = 'Y'
                                 AND a.pmj09 = (SELECT MAX(pmj09) 
                                                  FROM pmj_file c,
                                                       pmi_file d
												 WHERE c.pmj01 = d.pmi01 
												   AND d.pmicONf = 'Y'
												   AND a.pmj03 = c.pmj03
												   AND ROWNUM = 1
											   )
                                ) ON ima01 = a.pmj03 
                                  AND (a.pmj07 != 0 or a.pmj07 is not null)
  WHERE imaacti = 'Y'
    AND ima01 LIKE '02-SGM85-811';

在max那段加個rownum = 1 應該就能取到第一筆了
這是偷懶的作法，正確作法，應該要用sys_date去跟欄位的日期做判斷
接近的就是你要的資料

1、之後請養成sql排版，這樣子，才比較容易看的懂…
2、組SQL語法請盡量不要使用中文…

回應 6
分享
檢舉

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

lcw1960 iT邦新手 4 級 ‧ 2013-02-22 09:13:45 檢舉

料號最近採購價採購平均價市價幣別核價

02-SGM85-811 0 0 0 USD .43
02-SGM85-811 0 0 0 USD .415

我已經將您的SQL進入SQLPLUS執行, 結果還是抓到兩筆(如上面的結果), 並非我想抓的第二筆(即最近的核價);另外,我也有編排好SQL指令再貼到ithelp網上, 但顯示出來的語句就是不能像您有結構化的編排.
最後還是要再請網友幫忙,謝謝大家.

lcw1960 iT邦新手 4 級 ‧ 2013-02-22 09:46:44 檢舉

SELECT ima01 AS 料號,
ima53 AS 最近採購價,ima91 AS 採購平均價,
ima531 AS 市價,pmj05 AS 幣別,pmj07 AS 核價
FROM ima_file LEFT OUTER JOIN
(pmj_file a LEFT OUTER JOIN pmi_file b
ON a.pmj01 = b.pmi01 AND b.pmiconf = 'Y'
AND a.pmj09 = (SELECT MAX(pmj09)
FROM pmj_file c,
pmi_file d
WHERE c.pmj01 = d.pmi01
AND d.pmiconf = 'Y'
AND a.pmj03 = c.pmj03
AND ROWNUM = 1)
) ON ima01 = a.pmj03
AND (a.pmj07 != 0 or a.pmj07 is not null)
WHERE imaacti = 'Y'
AND ROWNUM = 1
AND ima01 LIKE '02-SGM85-811';

我再加了一行AND ROWNUM = 1,結果只抓出一筆,但卻是最早的核價而非最晚的核價,請網友在幫忙,謝謝.

lcw1960 iT邦新手 4 級 ‧ 2013-02-22 09:49:58 檢舉

USD .43是最早的一筆核價, USD .415則是最晚的核價, 我要能夠抓到最晚(最近)的核價,以該料號的最晚核價就是.415,所以請網友再幫忙看看.

lcw1960 iT邦新手 4 級 ‧ 2013-02-22 15:55:15 檢舉

SELECT ima01 AS 料號,
ima53 AS 最近採購價,ima91 AS 採購平均價,
ima531 AS 市價,pmj05 AS 幣別,pmj07 AS 核價
FROM ima_file LEFT OUTER JOIN pmj_file a
ON ima01 = a.pmj03
AND ROWNUM = (SELECT MAX(ROWNUM)
FROM pmj_file g,pmi_file h
WHERE g.pmj01 = h.pmi01
AND h.pmiconf = 'Y'
AND g.pmj03 = ima01
)
AND (a.pmj07 != 0 or a.pmj07 is not null)
WHERE imaacti = 'Y'
AND ima01 LIKE '02-SGM85-811';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
結果:
料號最近採購價採購平均價市價幣別核價