使用Pivot Table(Transact-SQL Cookbook)_pivot table for mysql-CSDN博客

1.1使用Pivot 表(注：Pivot Table有“数据透视表”和“枢轴表”两种翻译方法)

1.1.1问题
解决某些SQL问题时，经常需要对序列元素的支持。例如，给定一个时间段，为时间段中的每一天产生一行。或者，将行中的一系列值转换到同一行不同列中。为了实现这样的功能，你可以使用一个存储一系列顺序数字的常数表。这样的表就是Pivot表。

本书的很多示例都用到了Pivot 表，在很多情况下，表名就是Pivot。这个示例将会演示如何创建Pivot 表。

1.1.2解决方案

首先，创建Pivot 表。其次，创建名为Foo的表，使用它帮助生成Pivot表：

CREATE TABLE Pivot (

i INT ,

PRIMARY KEY (i)

)

CREATE TABLE Foo (

i CHAR ( 1 )

)

Foo表是一个简单的起支持作用的表，在其中插入10行：

INSERT INTO Foo VALUES ( ' 0 ' )

INSERT INTO Foo VALUES ( ' 1 ' )

INSERT INTO Foo VALUES ( ' 2 ' )

INSERT INTO Foo VALUES ( ' 3 ' )

INSERT INTO Foo VALUES ( ' 4 ' )

INSERT INTO Foo VALUES ( ' 5 ' )

INSERT INTO Foo VALUES ( ' 6 ' )

INSERT INTO Foo VALUES ( ' 7 ' )

INSERT INTO Foo VALUES ( ' 8 ' )

INSERT INTO Foo VALUES ( ' 9 ' )

使用Foo表中的10行，可以很容易在Pivot表中生成1000行。为了从10行生成1000行，连接Foo表到其自身3次，以创建迪卡尔积：

INSERT INTO Pivot

SELECT f1.i + f2.i + f3.i

FROM Foo f1, Foo f2, Foo f3

1.1.3 讨论

正如你将在本书后面的示例中看到的，Pivot表通常用来向查询添加顺序属性。Pivot表的某些形式可以在许多基于SQL的系统中找到，尽管它通常用户看不到它，它主要在预定义的查询或过程中使用。

你已经看到了表连接的次数是如何控制我们的INSERT语句在Pivot表中生成的行的数字的。从0到999的数值是通过连接字符串产生的。Foo表中的数字值是字符串。因此，使用加号(+)连接它们，我们得到以下结果：

' 0 ' + ' 0 ' + ' 0 ' = ' 000 '

' 0 ' + ' 0 ' + ' 1 ' = ' 001 '

...

这些结果插入目标Pivot表的整数列中。当你使用INSERT语句向整数列中插入字符串，数据库隐含地将这些字符串转换成数字。Foo表的迪卡尔积确保了生成所有可能的组合，因此，从0到999的所有可能的值都生成了。

值得指出的是这个例子中使用了0到999的行，并且没有负值。如果需要，你可以很容易地生成负值，通过在INSERT语句中在连接字符串前加入“-”符号，需要对0行多加一点小心。因为没有-0，因此你不会希望在生成负的Pivot数字时，插入“000”行。如果你这样做了，你只会在Pivot表中加入两个0行。在我们例子中，两个0行是不可能的，因为我们为我们的Pivot表定义了主键。

Pivot表可能是SQL世界中最有用的表。一旦你习惯于使用它，如果不使用它创建一个真实的SQL应用程序几乎是不可能的。作为一个演示，让我们使用Pivot表从编码32到126快速生成ASCII：

SELECT i Ascii_Code, CHAR (i) Ascii_Char FROM Pivot

WHERE i BETWEEN 32 AND 126

在这个例子中，使用Pivot表的好处就在于你生成了输入行，但是却不需要相同数量的行作为输入。如果没有Pivot表，这将是很困难的，甚至是不可能的。简单地通过描写一个范围，然后根据这个范围选择Pivot行，我们可以生成不存在于任何数据表中的数据。

另一个关于Pivot表的例子，我们可以很容易地生成今后7天的日历：