1.1使用Pivot 表(注:Pivot Table有“数据透视表”和“枢轴表”两种翻译方法)
1.1.1问题
解决某些SQL问题时,经常需要对序列元素的支持。例如,给定一个时间段,为时间段中的每一天产生一行。或者,将行中的一系列值转换到同一行不同列中。为了实现这样的功能,你可以使用一个存储一系列顺序数字的常数表。这样的表就是Pivot表。
本书的很多示例都用到了Pivot 表,在很多情况下,表名就是Pivot。这个示例将会演示如何创建Pivot 表。
1.1.2解决方案
首先,创建Pivot 表。其次,创建名为Foo的表,使用它帮助生成Pivot表:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Foo表是一个简单的起支持作用的表,在其中插入10行:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
使用Foo表中的10行,可以很容易在Pivot表中生成1000行。为了从10行生成1000行,连接Foo表到其自身3次,以创建迪卡尔积:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
1.1.3 讨论
正如你将在本书后面的示例中看到的,Pivot表通常用来向查询添加顺序属性。Pivot表的某些形式可以在许多基于SQL的系统中找到,尽管它通常用户看不到它,它主要在预定义的查询或过程中使用。
你已经看到了表连接的次数是如何控制我们的INSERT语句在Pivot表中生成的行的数字的。从0到999的数值是通过连接字符串产生的。Foo表中的数字值是字符串。因此,使用加号(+)连接它们,我们得到以下结果:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
这些结果插入目标Pivot表的整数列中。当你使用INSERT语句向整数列中插入字符串,数据库隐含地将这些字符串转换成数字。Foo表的迪卡尔积确保了生成所有可能的组合,因此,从0到999的所有可能的值都生成了。
值得指出的是这个例子中使用了0到999的行,并且没有负值。如果需要,你可以很容易地生成负值,通过在INSERT语句中在连接字符串前加入“-”符号,需要对0行多加一点小心。因为没有-0,因此你不会希望在生成负的Pivot数字时,插入“000”行。如果你这样做了,你只会在Pivot表中加入两个0行。在我们例子中,两个0行是不可能的,因为我们为我们的Pivot表定义了主键。
Pivot表可能是SQL世界中最有用的表。一旦你习惯于使用它,如果不使用它创建一个真实的SQL应用程序几乎是不可能的。作为一个演示,让我们使用Pivot表从编码32到126快速生成ASCII:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
在这个例子中,使用Pivot表的好处就在于你生成了输入行,但是却不需要相同数量的行作为输入。如果没有Pivot表,这将是很困难的,甚至是不可能的。简单地通过描写一个范围,然后根据这个范围选择Pivot行,我们可以生成不存在于任何数据表中的数据。
另一个关于Pivot表的例子,我们可以很容易地生成今后7天的日历:
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)