一道面試題

有一張表兩個字段：_date，_num，

現在要產生如下的表格，請設計相關SQL語句。

 

年份

1月

2月

3月

4月

5月

6月

7月

8月

9月

10月

11月

12月

2010

數量

數量

數量

數量

數量

數量

數量

數量

數量

數量

數量

數量

2011

數量

數量

數量

數量

數量

數量

數量

數量

數量

數量

數量

數量

……

 

 

sql code

 

--創建測試表

CREATE TABLE Test

(

    _date DATETIME,

    _num INT

)

 

 

--插入測試數據

INSERT  dbo.Test

SELECT '2010-01-01',100 UNION ALL

SELECT '2010-02-01',100 UNION ALL

SELECT '2010-03-01',100 UNION ALL

SELECT '2010-04-01',200 UNION ALL

SELECT '2010-05-01',300 UNION ALL

SELECT '2010-06-01',400 

 

 

SELECT * FROM dbo.Test

 

 

 

 

 

--創建臨時表

CREATE TABLE #table

(

_year INT,

_month INT,

_num INT

)

 

 

 

--為臨時表插入數據

INSERT #table

SELECT

YEAR(t.[_date]),

MONTH(t.[_date]),

SUM(t._num)

FROM dbo.Test t

GROUP BY YEAR(t.[_date]),MONTH(t.[_date]) 

 

 

--查詢結果如下

SELECT t.[_year] '年份', 

    SUM(CASE t.[_month] WHEN 1 THEN t.[_num] ELSE 0 END) AS '1月',

    SUM(CASE t.[_month] WHEN 2 THEN t.[_num] ELSE 0 END) AS '2月',

    SUM(CASE t.[_month] WHEN 3 THEN t.[_num] ELSE 0 END) AS '3月',

    SUM(CASE t.[_month] WHEN 4 THEN t.[_num] ELSE 0 END) AS '4月',

    SUM(CASE t.[_month] WHEN 5 THEN t.[_num] ELSE 0 END) AS '5月',

    SUM(CASE t.[_month] WHEN 6 THEN t.[_num] ELSE 0 END) AS '6月',

    SUM(CASE t.[_month] WHEN 7 THEN t.[_num] ELSE 0 END) AS '7月',

    SUM(CASE t.[_month] WHEN 8 THEN t.[_num] ELSE 0 END) AS '8月',

    SUM(CASE t.[_month] WHEN 9 THEN t.[_num] ELSE 0 END) AS '9月',

    SUM(CASE t.[_month] WHEN 10 THEN t.[_num] ELSE 0 END) AS '10月',

    SUM(CASE t.[_month] WHEN 11 THEN t.[_num] ELSE 0 END) AS '11月',

    SUM(CASE t.[_month] WHEN 12 THEN t.[_num] ELSE 0 END) AS '12月'

FROM #table t GROUP BY t.[_year]

 

 

 

--刪除臨時表

DROP TABLE #table

  

 

經過研究，其實可以更簡單，採用數據透視方法。

 

code如下：

 

declare @sql varchar(max)

set @sql = 'select year(_date) [年份]'

select @sql = @sql + ',sum(case month(_date) when ''' + ltrim(_date) + ''' then _num else 0 end) [' + ltrim(_date) + '月]'

from (select month(_date) _date from dbo.Test group by month(_date)) t

select @sql = @sql + ' from Test group by year(_date)'

exec(@sql)