SQL研究 - Pivot

在一个风和日丽的早上，老板从邮箱里发给你一个任务，希望你根据一个投资记录表做出一个统计。

你匆匆跑到指定的数据库，发现结构如下的一张表：

CREATE TABLE [dbo].[Investment](
 [Investor] [nvarchar](256) NULL,
 [Capital] [float] NULL,
 [District] [nvarchar](256) NULL
)

你按照惯例看了下内容：select * from [dbo].[Investment]

得到如下结果：

Investor Capital District

IBM 1000000 Asia
IBM 150000 Europe
Oracle 800000 Asia
Oracle 180000 Europe
Microsoft 900000 Asia
Microsoft 180000 Europe

 

你的第一反应当然是：

select Investor, District,sum(Capital) from [dbo].[Investment] group by Investor, District

 

不过你很快发现，这样做根本没什么变化，而且也体现不出你的能力，嘿嘿，我们总是认为自己很有能力的。

 

的确，有个方法可以得到更酷的结果，那就是Pivot：

select Investor,[Asia],[Europe]
from

( select Investor,Capital,District from dbo.Investment ) x

pivot
( sum(Capital)  for District in([Asia],[Europe]) ) as pvt

 

结果如下：

Investor Asia Europe

IBM 1000000 150000
Microsoft 900000 180000
Oracle 800000 180000

很酷吧。

 

现在来分析一下Pivot是怎样来得到这个结果的。

首先，Pivot的输入是什么，当然就是第三行的Select语句，请注意到，它的结果集包含3个列；

然后，Pivot把哪列转到列头上去，请注意第五行关键字For后的值：District；注意并不是把该列的所有值转到列头上，仅限于 IN 后被“（）”包围的值。

细节：Asia和Europe出现了两次，第一行和第五行，含义并不相同，在第一行是作为列名，在第五行作为值。但是它们都被“[]”包围，而不是字符串常用的单引号，请不要忽略这一点。

最后，我们发现源表的Capital列消失了，新增加了Aisa和Europe列，那么新表中的表格单元值是什么呢？ 是由第五行的聚合函数sum确定的，正如你所知道的那样，每个聚合函数都是在分组上工作的，此处的分组，即group by的目标是什么呢？

 

是源表中既没被删除，也没被转变的那一列：Investor。

 

最后，为了突出Pivot的好处，让我们看看如果没有Pivot，要想得到同样的结果，SQL是怎样的？

select Investor,
   case when District='Asia' then sum(Capital)
     else 0
   end as [Asia],
   case when District='Europe' then sum(Capital)
      else 0
   end as [Europe]
from dbo.Investment
group by Investor,District

 这样得不到最终结果，不过已经很接近了：

Investor Asia Europe

IBM 1000000 0
Microsoft 900000 0
Oracle 800000 0
IBM 0 150000
Microsoft 0 180000
Oracle 0 180000

 

接下来，我们只需要这样：

select Investor,sum(Asia),sum(Europe)

from #temp

group by Investor

虽然最终得到结果，不过比起Pivot，还是要麻烦很多。