學習紀錄

這幾天剛好在看一些SQL的相關統計的用法。發現在2005的時候就有增加了一個非常好用的語法

有空來改掉某幾隻的SQL語法，長到讓人不知道怎麼維護

統計 時段品號加總 ..

SELECT TB010 AS 品號, MB002 AS 品名,
ISNULL(Q1, 0) as '第一季', ISNULL(Q2, 0) as '第二季', ISNULL(Q3, 0) as '第三季', ISNULL(Q4, 0) as '第四季'
FROM (
SELECT
Case
  when SUBSTRING(TB001, 5,2) ='01' then 'Q1'
  when SUBSTRING(TB001, 5,2) ='02' then 'Q1'
  when SUBSTRING(TB001, 5,2) ='03' then 'Q1'
  when SUBSTRING(TB001, 5,2) ='04' then 'Q2'
  when SUBSTRING(TB001, 5,2) ='05' then 'Q2'
  when SUBSTRING(TB001, 5,2) ='06' then 'Q2'
  when SUBSTRING(TB001, 5,2) ='07' then 'Q3'
  when SUBSTRING(TB001, 5,2) ='08' then 'Q3'
  when SUBSTRING(TB001, 5,2) ='09' then 'Q3'
  when SUBSTRING(TB001, 5,2) ='10' then 'Q4'
  when SUBSTRING(TB001, 5,2) ='11' then 'Q4'
  when SUBSTRING(TB001, 5,2) ='12' then 'Q4'
else null End as N'季',
TB010, MB002, SUM(TB033) as '季加總'
FROM dbo.POSTB
LEFT JOIN INVMB ON MB001 = TB010
GROUP BY TB001,TB010, MB002
) as GroupTable
PIVOT
(
Sum(季加總)
FOR 季 IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable

呈現結果