[笔记][MSSQL]关于枢纽分析查询的使用

之前在实务上有做到一个功能，大略是要统计每个人在一年当中每个月的请假天数，本来有想过用12个子查询分别去SELECT出每个月的请假天数，但是那时候的我怎么串都串不出想要的资料来，就去翻书看到了一个用枢纽分析查询作出来的统计表，今天来作一下笔记。

以下使用北风资料库的[Orders]和[Order Details]做测试：

首先我们需要得出在今年份(以1997年为例)每个月各产品的销售额

SELECT YEAR(M.OrderDate) o_year,MONTH(M.OrderDate) o_month,P.ProductName,SUM(D.Quantity*D.UnitPrice) totalFROM [Orders] M LEFT JOIN [Order Details] D ON M.OrderID=D.OrderIDLEFT JOIN [Products] P ON D.ProductID=P.ProductIDWHERE YEAR(M.OrderDate)='1997'GROUP BY YEAR(M.OrderDate),MONTH(M.OrderDate),P.ProductName

结果会如下(红框处等等会解释，可以先忽略)：

而枢纽分析查询要做的事情就是要将这张表依照条件，将现在为纵轴的月份转为横轴，而产品名称依旧维持在纵轴，并将该月份的销售额填进该月份(横轴)与产品名称(纵轴)的对应栏位中。

这样说可能有点抽象，直接从SQL语法和结果来看会比较清楚：

--这边是定义枢纽分析报表的栏位名称，必须是要转为枢纽分析表的资料表拥有的栏位，也就是栏位名称必须和以下FROM的来源资料表内的栏位名称相同(取别名和其他函式的用法都相同，以下是把月份以简写取别名，AS可省略)。--还有要注意的是第一行的ProductName他不算是枢纽分析的资料行，可以把它当成是纵轴的栏位，而之后的月份为横轴的栏位。SELECT ProductName,ISNULL([1],0) AS Jan,ISNULL([2],0) AS Feb,ISNULL([3],0) AS Mar,ISNULL([4],0) AS Apr,ISNULL([5],0) AS May,ISNULL([6],0) AS Jun,ISNULL([7],0) AS Jul,ISNULL([8],0) AS Aug,ISNULL([9],0) AS Sep,ISNULL([10],0) AS Oct,ISNULL([11],0) AS Nov,ISNULL([12],0) AS Dec--这边的来源资料表是上一步查询出来的当年度每个月个产品的销售资料FROM(SELECT YEAR(M.OrderDate) o_year,MONTH(M.OrderDate) o_month,P.ProductName,SUM(D.Quantity*D.UnitPrice) totalFROM [Orders] M LEFT JOIN [Order Details] D ON M.OrderID=D.OrderIDLEFT JOIN [Products] P ON D.ProductID=P.ProductIDWHERE YEAR(M.OrderDate)='1997'GROUP BY YEAR(M.OrderDate),MONTH(M.OrderDate),P.ProductName) MyTable--需要彙总的栏位，和以哪个栏位下去彙总--在这边我要算每个月的合计所以我就合计来源资料表内的total栏位，然后FOR月份内对应的资料下去彙总计算，所以这边IN的内容就是第一张结果资料表的红框处内的资料。--这里IN的资料会对应到上面枢纽分析报表的栏位名称，所以在一开始SELECT的时候要先知道要FOR哪个资料作彙总，别名在另外取就好。PIVOT(SUM(Mytable.total)FOR MyTable.o_monthIN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pivORDER BY ProductName

结果会如下：

是不是变的超漂亮的!
而且SQL也不会下的很複杂，当初看到这个方法的时候一整个觉得赚到了，只是之后也没有在用过了，如果有机会各位大大也可以试试看!

Function：
1.ISNULL(AAA,BBB)
这个函式会去判断AAA如果是NULL的话会把他替代成BBB，BBB可以是'字串'也能是栏位。
2.YEAR(AAA)
不论AAA是什么形态，函式会先将AAA转为日期/时间格式，在取出年度。
3.MONTH(AAA)
不论AAA是什么形态，函式会先将AAA转为日期/时间格式，在取出月份。

题外话，这篇有些地方可能解释的不太清楚，也不知道怎么说会比较明了，
所以如果有疑问可以留言，我会尽力回答补充的!