SQL語法求教

sql

sphinx 2010-05-07 13:50:30 ‧ 7328 瀏覽

各位大大：
請教一個SQL命令的寫法，資料表abc中有三個欄位：id（貨號）、ym（月份）、qty（出貨數量），能否用一個SQL指令找出指定月份（假設為201005）有出貨的貨品id，其當月份出貨數量qty、年初至指定月份（即201001~201005）的出貨總和qty_year以及近3年（即200701~201005）的出貨總和qty_3year？

外獅佬 iT邦大師 1 級 ‧ 2010-05-07 15:41:16 檢舉

你這個要用一條SQL寫？？別太勉強了...時間區間條件都不太一樣...

sphinx iT邦研究生 3 級 ‧ 2010-05-07 17:22:14 檢舉

因為對SQL語法不太熟悉，以為這是可以用一條命令解決掉的，但看來似乎是提了個沒營養的問題，真是不好意思，晚點我再把它刪掉...^^"

外獅佬 iT邦大師 1 級 ‧ 2010-05-07 17:33:55 檢舉

留著吧～這裡很多神通廣大的大師，相信他們應該有更好的見解的。

登入發表討論

直播研討會

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

直播中

3 個回答

pojen

iT邦研究生 5 級 ‧ 2010-05-08 00:14:31

最佳解答

選當月的資料應該不算太糟, 總額的話我會如此用:

create table pojen_table (
id integer,
ym datetime,
qty integer)

insert into pojen_Table values(1,getdate(),3)
insert into pojen_table values(2,getdate(),5)
insert into pojen_table values(3,dateadd(dd,-50,getdate()),3)
insert into pojen_table values(4,dateadd(yy,-2,getdate()),5)

select * from pojen_table

declare @emydate datetime
set @emydate = convert(datetime,'05/11/2010',101);

declare @3yearsago datetime
select @3yearsago= DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR,-3,@emydate)), 0) ;

select @3yearsago,@emydate;

with t as(
select datepart(year,ym) ecumyear ,datepart(year,ym)*100+datepart(month,ym) ecurrentyear, sum(qty) eqty
from pojen_table
where ym between @3yearsago AND @emydate
group by datepart(year,ym),datepart(year,ym)*100+datepart(month,ym) with rollup
)
select *
from t
where (ecumyear = datepart(year,@emydate) and ecurrentyear = (datepart(year,@emydate)*100+datepart(month,@emydate)))
OR (ecumyear = datepart(year,@emydate) and ecurrentyear is null)
or (ecumyear is null)

drop table pojen_table

然後可以用 union 粘上當月日期與 id

回應
分享
檢舉

登入發表回應

jamesjan

iT邦高手 1 級 ‧ 2010-05-07 21:25:37

基本上有幾種作法
您可以將個別區間的查詢寫作成三個 view 或三個 query, 然後針對 id 做 join

&lt;pre class="c" name="code">select p.id,p.qty,p.qty_year,p.qty_3year from ((
select id,sum(qty) as qty where id=your_parts and ym='201005') a inner join (
select id,sum(qty) as qty_year where id=your_parts and ym between '201001' and '201005') b on a.id=b.id inner join (
select id,sum(qty) as qty_3year where id=your_parts and ym between '200701' and '201005') c on a.id=c.id) p

也可以透過 subquery 的方式，取得需要的sum, 但資料量大時，這種作法的效能會較不好

&lt;pre class="c" name="code">select p.id,sum(qty) as qty,
(select sum(qty) from abc where id=p.id and ym between '201001' and '201005') as qty_year,
(select sum(qty) from abc where id=p.id and ym between '200701' and '201005') as qty_year,
from abc p
where p.id=your_parts and ym='201005'

可以藉由 excuteion plan 看一下執行的效能，比較一下

回應 4
分享
檢舉

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

sphinx iT邦研究生 3 級 ‧ 2010-05-07 22:16:37 檢舉

非常謝謝您的回覆，可是從您的指令看起來，似乎是找出某項單一貨品(your_parts)的出貨總和，但我想要的是能夠列出201005所有出貨項目的數量，以及這些貨品過去的出貨總和...這個問題是不是應該利用view來解決比較適當？否則搞出一個落落長的SQL命令，徒增日後維護的困擾...但還是希望能夠看到高手寫出的精彩指令，說不定可以讓人增加一甲子的功力呢～

jamesjan iT邦高手 1 級 ‧ 2010-05-09 10:29:35 檢舉

把 id=your_part 的條件拿掉就是所有出貨項目的數量統計了

jamesjan iT邦高手 1 級 ‧ 2010-05-09 10:32:31 檢舉

sorry 少了 group by

&lt;pre class="c" name="code">select p.id,sum(qty) as qty,  
(select sum(qty) from abc where id=p.id and ym between '201001' and '201005') as qty_year,  
(select sum(qty) from abc where id=p.id and ym between '200701' and '201005') as qty_year,  
from abc p  
where ym='201005'
group by id

這是比較舊的 sql 寫法，sql 2008 好像有支援比較簡便的作法
但是已經沒有再去研究了

jamesjan iT邦高手 1 級 ‧ 2010-05-09 10:34:12 檢舉

&lt;pre class="c" name="code">select p.id,sum(qty) as qty,    
(select sum(qty) from abc where id=p.id and ym between '201001' and '201005') as qty_year,    
(select sum(qty) from abc where id=p.id and ym between '200701' and '201005') as qty_3year,    
from abc p    
where ym='201005'  
group by p.id

登入發表回應