線上訂房服務-台灣趴趴狗聯合訂房中心
發文 回覆 瀏覽次數:7787
推到 Plurk!
推到 Facebook!

SQL語法如何一次統計出1個年度12個月的銷售金額?(ORACLE)

答題得分者是:wilsonning
cindyliu
一般會員


發表:16
回覆:18
積分:6
註冊:2008-09-17

發送簡訊給我
#1 引用回覆 回覆 發表時間:2009-01-05 22:36:59 IP:118.166.xxx.xxx 訂閱
客戶檔 (CUST)
cus_no cus_nm
---------- ------------
A001 ABCD
A002 XYZ
A003 QOO


銷售檔 (SALES)
s_date cus_no amt
--------------- ----------- ----------
20071110 A001 100
20071223 A003 200
20080115 A001 500
20080220 A003 160
20080415 A002 100
20080420 A002 30
..........


SQL要如何依選擇的年度不同,而一次產出12個月的統計資料?
例如:選擇97年度時,產出的資料就像這下面這樣(以客戶檔為主TABLE):

(需要用到暫存TABLE 嗎? 如何使用?)

客戶編號 客戶名稱 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
------------ -------------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------ ------- -------
A001 ABCD 500 0 0 0 0 0 0 0 0 0 0 0
A002 XYZ 0 0 0 130 0 0 0 0 0 0 0 0
A003 QOO 0 160 0 0 0 0 0 0 0 0 0 0

請大家幫忙指點一下..謝謝^^
編輯記錄
cindyliu 重新編輯於 2009-01-05 23:21:28, 註解 無‧
st33chen
尊榮會員


發表:15
回覆:591
積分:1201
註冊:2005-09-30

發送簡訊給我
#2 引用回覆 回覆 發表時間:2009-01-06 08:43:58 IP:122.116.xxx.xxx 未訂閱
您好,

11g 的話可以用 pivot (sorry, 我沒 11g 環境所以沒實測, 我想大約是這樣, 如果不對, 自行修一下) :

select a.cus_no, b.cus_nm, a.amt_jan, a.amt_feb, ...
from (
select s_date, cus_no, amt from sales where s_date like :yyyy
) pivot (max(amt) for substr(s_date,5,2) in ('01' as amt_jan, '02' as amt_feb, ...)) a, cust b
where a.cus_no=b.cus_no( )

不能用 pivot 的話, 可以用 decode 或 case ( sorry, 一樣沒實測 )
select a.cus_no, b.cus_nm, a.amt_jan, a.amt_feb, ...
from (
select cus_no,
sum(decode(substr(s_date,5,2),'01',amt,0)) amt_jan,
sum(decode(substr(s_date,5,2),'02',amt,0)) amt_feb,
...
where s_date like :yyyy
group by cus_no
) a, cust b
where a.cus_no=b.cus_no( )
照理, 還可以整併成一個 select, 但我覺得這樣寫比較清楚.

參考一下

==============================================
還有一個方法, 也可試試, (因為用了很多select, 效率說不定較差)
select a.cus_no, b.cus_nm, sum(amt_jan) amt_jan, sum(amt_feb) amt_feb, ...
from (
-- 第一個 select 的 0 的部份要指定欄位名稱, 第二個 select 及以後可以不指定
select cus_no, amt amt_jan, 0 amt_feb, 0 amt_mar, ... from sales where s_date like :yyyy || '01%'
union all
select cus_no, 0, amt, 0, ... from sales where s_date like :yyyy || '02%'
union all
select cus_no, 0, 0, amt, 0, ... from sales where s_date like :yyyy || '03%'
...
) a, cust b
where a.cus_no=b.cus_no( )
group by a.cus_no, b.cus_nm

------
IS IT WHAT IT IS
我是 李慕白 請倒著唸.
又想把老話拿出來說, 請用台語發音 : 專家專家全是ROBOT CAR (滷肉腳啦);
都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲.
編輯記錄
st33chen 重新編輯於 2009-01-06 14:31:02, 註解 無‧
st33chen 重新編輯於 2009-01-06 14:35:11, 註解 無‧
pedro
尊榮會員


發表:152
回覆:1177
積分:875
註冊:2002-06-12

發送簡訊給我
#3 引用回覆 回覆 發表時間:2009-01-06 09:07:54 IP:60.248.xxx.xxx 未訂閱
group by 年月,sum起來
再用直轉橫的方法
http://delphi.ktop.com.tw/board.php?cid=168&fid=918&tid=95404

===================引 用 cindyliu 文 章===================
SQL要如何依選擇的年度不同,而一次產出12個月的統計資料?
例如:選擇97年度時,產出的資料就像這下面這樣(以客戶檔為主TABLE):
wilsonning
一般會員


發表:0
回覆:1
積分:5
註冊:2005-07-25

發送簡訊給我
#4 引用回覆 回覆 發表時間:2009-01-08 14:56:39 IP:124.8.xxx.xxx 訂閱
使用以下的語法,效能會比union十二次還要好多了

select b.cus_no, b.cus_nm
,(case when substr(s_date,5,2)='01' then sum(a.amt) end) amt_jan
,(case when substr(s_date,5,2)='02' then sum(a.amt) end) amt_feb
,(case when substr(s_date,5,2)='03' then sum(a.amt) end) amt_mar
...
from SALES a
join CUST b on a.cus_no=b.cus_no
where substr(a.s_date,1,4)='2008'
group by b.cus_no, b.cus_nm
order by b.cus_no, b.cus_nm
系統時間:2018-01-20 8:54:22
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!