原來的表單是 編號----轉入--------轉出 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

引言: 原來的表單是 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);

引言:

引言: 原來的表單是 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);

樓上的高手你好，我試過了，不過他出現 伺服器: 訊息 156，層級 15，狀態 1，行 1 關鍵字 'FROM' 附近的語法不正確。 另外我想問一下EXTRACT是什麼意思，謝謝 還有我的二個日期是各為一個欄位哦^^"

引言: 樓上的高手你好，我試過了，不過他出現 伺服器: 訊息 156，層級 15，狀態 1，行 1 關鍵字 'FROM' 附近的語法不正確。 另外我想問一下EXTRACT是什麼意思，謝謝 還有我的二個日期是各為一個欄位哦^^"

請依你資料庫sql語法自行調整。 extract是從日期欄位取出 年、月或日的function 應該是以一個日期欄位為準。

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

timhuang你好： 你的語法是寫成轉入的比數，那個我是完成了 我現在要的是，該一筆轉入到轉出的日數最後還在做一個合計功能， 然後chinyu大大寫的偶實在是看不懂，可能資料庫不同吧，extract一直無法使用 麻煩您在幫我想想囉，謝謝

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 編號