iT邦幫忙

0

SQL語法求教

sql

各位大大:
請教一個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 檢舉
留著吧~這裡很多神通廣大的大師,相信他們應該有更好的見解的。
14
pojen
iT邦新手 2 級 ‧ 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

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

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

<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, 但資料量大時,這種作法的效能會較不好

<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 看一下執行的效能,比較一下

看更多先前的回應...收起先前的回應...
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

<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 檢舉
<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
6
ansonchen
iT邦新手 1 級 ‧ 2010-05-14 19:45:22

小弟有段時間沒玩DB
依你的需求小弟認為
應可使用SQL Join
SQL join分 Left Join / Right Join / Inner Join
你可用Inner Join多table作where查詢
小弟若有空在幫你試試

我要發表回答

立即登入回答