請高手幫忙一個SQL語法問題 |
答題得分者是:timhuang
|
iii928
一般會員 發表:19 回覆:10 積分:5 註冊:2004-06-04 發送簡訊給我 |
原來的表單是
編號----轉入--------轉出
0001--2004/1/3----2004/1/5
0001--2004/1/11---2004/1/15
0002--2004/1/15---2004/1/18
0002--2004/1/21---2004/1/22
0002--2004/2/2----2004/2/5 要變成這樣
編號 JAN FEB ........ TOTAL
0001 6 0 ........ 6
0002 4 3 ........ 7
請高手幫幫忙解一個語法如何寫呢 發表人 - iii928 於 2004/08/01 16:53:26
|
chinyu
高階會員 發表:12 回覆:157 積分:153 註冊:2002-06-14 發送簡訊給我 |
引言: 原來的表單是 0001 2004/1/3 2004/1/5 0001 2004/1/11 2004/1/15 0002 2004/1/15 2004/1/18 0002 2004/1/21 2004/1/22 0002 2004/2/2 2004/2/5 要變成這樣 編號 JAN FEB ........ TOTAL 0001 6 0 ........ 6 0002 4 3 ........ 7 請高手幫幫忙解一個語法如何寫呢資料庫Firebird 1.5 case的應用 SELECT EXTRACT(YEAR FROM S_MONTH) AS S_YEAR, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '01' THEN S_SELL ELSE 0 END) AS MONTH1, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '02' THEN S_SELL ELSE 0 END) AS MONTH2, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '03' THEN S_SELL ELSE 0 END) AS MONTH3, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '04' THEN S_SELL ELSE 0 END) AS MONTH4, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '05' THEN S_SELL ELSE 0 END) AS MONTH5, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '06' THEN S_SELL ELSE 0 END) AS MONTH6, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '07' THEN S_SELL ELSE 0 END) AS MONTH7, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '08' THEN S_SELL ELSE 0 END) AS MONTH8, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '09' THEN S_SELL ELSE 0 END) AS MONTH9, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '10' THEN S_SELL ELSE 0 END) AS MONTH10, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '11' THEN S_SELL ELSE 0 END) AS MONTH11, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '12' THEN S_SELL ELSE 0 END) AS MONTH12 FROM SALE GROUP BY EXTRACT(YEAR FROM S_MONTH); |
iii928
一般會員 發表:19 回覆:10 積分:5 註冊:2004-06-04 發送簡訊給我 |
引言:樓上的高手你好,我試過了,不過他出現 伺服器: 訊息 156,層級 15,狀態 1,行 1 關鍵字 'FROM' 附近的語法不正確。 另外我想問一下EXTRACT是什麼意思,謝謝 還有我的二個日期是各為一個欄位哦^^"引言: 原來的表單是 0001 2004/1/3 2004/1/5 0001 2004/1/11 2004/1/15 0002 2004/1/15 2004/1/18 0002 2004/1/21 2004/1/22 0002 2004/2/2 2004/2/5 要變成這樣 編號 JAN FEB ........ TOTAL 0001 6 0 ........ 6 0002 4 3 ........ 7 請高手幫幫忙解一個語法如何寫呢資料庫Firebird 1.5 case的應用 SELECT EXTRACT(YEAR FROM S_MONTH) AS S_YEAR, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '01' THEN S_SELL ELSE 0 END) AS MONTH1, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '02' THEN S_SELL ELSE 0 END) AS MONTH2, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '03' THEN S_SELL ELSE 0 END) AS MONTH3, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '04' THEN S_SELL ELSE 0 END) AS MONTH4, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '05' THEN S_SELL ELSE 0 END) AS MONTH5, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '06' THEN S_SELL ELSE 0 END) AS MONTH6, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '07' THEN S_SELL ELSE 0 END) AS MONTH7, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '08' THEN S_SELL ELSE 0 END) AS MONTH8, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '09' THEN S_SELL ELSE 0 END) AS MONTH9, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '10' THEN S_SELL ELSE 0 END) AS MONTH10, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '11' THEN S_SELL ELSE 0 END) AS MONTH11, SUM(CASE EXTRACT(MONTH FROM S_MONTH) WHEN '12' THEN S_SELL ELSE 0 END) AS MONTH12 FROM SALE GROUP BY EXTRACT(YEAR FROM S_MONTH); |
chinyu
高階會員 發表:12 回覆:157 積分:153 註冊:2002-06-14 發送簡訊給我 |
|
timhuang
尊榮會員 發表:78 回覆:1815 積分:1608 註冊:2002-07-15 發送簡訊給我 |
Hi, 由你的錯誤訊息看來應該是 mssql server 的資料庫, 接下來就好辦囉, 使用 mssql server 的取出月份的語法即可, 假設以轉入為條件, 可以這樣下,
select 編號, sum(when month(轉入)=1 then 1 else 0 end) as JAN, sum(when month(轉入)=2 then 1 else 0 end) as FEB, sum(when month(轉入)=3 then 1 else 0 end) as MAR, .... sum(when month(轉入)=12 then 1 else 0 end) as DEC, sum(1) as TOTAL from your_table group by 編號發表人 - timhuang 於 2004/08/01 21:15:36 |
iii928
一般會員 發表:19 回覆:10 積分:5 註冊:2004-06-04 發送簡訊給我 |
|
timhuang
尊榮會員 發表:78 回覆:1815 積分:1608 註冊:2002-07-15 發送簡訊給我 |
EXTRACT(MONTH FROM 轉入) 就是 mssql 的 month(轉入) , 所以我誤會了你的問題, 若計量值是日數, 那就用 datediff 即可, 稍微修改一下,
select 編號, sum(when month(轉入)=1 then datediff(d, 轉入, 轉出) else 0 end) as JAN, sum(when month(轉入)=2 then datediff(d, 轉入, 轉出) else 0 end) as FEB, sum(when month(轉入)=3 then datediff(d, 轉入, 轉出) else 0 end) as MAR, .... sum(when month(轉入)=12 then datediff(d, 轉入, 轉出) else 0 end) as DEC, sum(datediff(d, 轉入, 轉出)) as TOTAL from your_table group by 編號 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |