数据库

所谓表旋转，就是将表的行转换为列，或是将表的列转换为行，这是从SQL Server 2005开始提供的新技术。因此，如果希望使用此功能，需要将数据库的兼容级别设置为90。表旋转在某些方面也是解决了表的数据存储和实际需要之间的矛盾。例如，图9-4所示的是一个典型的产品销售统计表，这种格式虽然便于阅读，但是在进行数据表存储的时候却并不容易管理，产品销售数据表通常需要设计成图9-5所示的结构。这样就带来一个问题，用户既希望数据容易管理，又希望能够生成一种能够容易阅读的表格数据，这时候就可以使用表旋转技术。

  图9-4 产品销售表     

  图9-5 数据表结构

9.4.1 PIVOT运算符

PIVOT运算符用于将表的行转换为列，并能同时对行执行聚合运算。其语法格式如下：

SELECT <非旋转列>,

    [第一个旋转列] AS <列名>,

    [第二个旋转列] AS <列名>,

    ...

    [最后的旋转列] AS <列名>

FROM

    (生成数据的查询>) AS <为源查询结果指定的别名>

 

PIVOT

(

    <聚合函数>(<被聚合的列>)

FOR

[<包含将被转换为列标头的值的列>]

    IN ( [第一个旋转后的列], [第二个旋转后的列],

    ... [最后一个旋转后的列])

) AS <为旋转表指定的别名>

 

<可选的 ORDER BY 子句>;

为了实现行的旋转，源查询获得的结果应当具备三列，才能够实现旋转。第1列是不进行旋转的列，属于标志列；第2列是属性列，也称为透视列，其中的值会被旋转列名；第3列是属性值列，这些值将作为新列的值。使用下面的语句创建一个示例表Orders，内容如表9-7所示。

CREATE TABLE Orders

(

   ProductID int NOT NULL,

   OrderDate datetime NOT NULL,

   ShipTo char(20) NOT NULL,

   SubTotal money NOT NULL

);

INSERT INTO Orders

VALUES (1,CAST('20090102' AS datetime), 'Shanghai', 100.00),

       (1, CAST('20090105' AS datetime), 'Shanghai',100.00),

       (1, CAST('20090123' AS datetime),'Jinan',    100.00),

       (2, CAST('20090125' AS datetime),'Shanghai', 100.00),

       (1, CAST('20090205' AS datetime),'Jinan',    100.00),

       (3, CAST('20090213' AS datetime),'Shanghai', 100.00),

       (3, CAST('20090219' AS datetime),'Shanghai', 100.00),

       (4, CAST('20090309' AS datetime),'Beijing',  100.00),

       (1, CAST('20090311' AS datetime),'Dalian',   100.00),

       (2, CAST('20090324' AS datetime),'Shanghai', 100.00),

       (3, CAST('20090326' AS datetime),'Wuhan',    100.00);

表9-7                                                                     Orders表的内容

ProductID	OrderDate	ShipTo	SubTotal
1	2009-01-02 00:00:00.000	Shanghai	100.00
1	2009-01-05 00:00:00.000	Shanghai	100.00
1	2009-01-23 00:00:00.000	Jinan	100.00
2	2009-01-25 00:00:00.000	Shanghai	100.00
1	2009-02-05 00:00:00.000	Jinan	100.00
3	2009-02-13 00:00:00.000	Shanghai	100.00
3	2009-02-19 00:00:00.000	Shanghai	100.00
4	2009-03-09 00:00:00.000	Beijing	100.00
1	2009-03-11 00:00:00.000	Dalian	100.00
2	2009-03-24 00:00:00.000	Shanghai	100.00
3	2009-03-26 00:00:00.000	Wuhan	100.00

Orders表中包含了3个月的产品销售数据，现在假设要获得像图9-4所示的销售表，则对源表的查询首先需要获得上面讲的三列，参考下面的语句：

SELECT ProductID,

       MONTH(OrderDate) AS OrderMonth,

       SubTotal

FROM Orders;

查询结果如表9-8所示。其中ProductID为标志列，OrderMonth为属性列，其中的月份要转变为列的名称，SubTotal为属性值列，这些值将成为新列的值。

表9-8                                                                   获取到的三列内容

ProductID	OrderMonth	SubTotal
1	1	100.00
1	1	100.00
1	1	100.00
2	1	100.00
1	2	100.00
3	2	100.00
3	2	100.00
4	3	100.00
1	3	100.00
2	3	100.00
3	3	100.00

完整的旋转查询语句如下。查询结果如表9-9所示。

SELECT ProductID,

       [1]AS Jan,

       [2]AS Feb,

       [3]AS Mar

FROM (SELECT ProductID, MONTH(OrderDate) ASOrderMonth, SubTotal

      FROMOrders) AS O1

PIVOT

(

  SUM(SubTotal)

   FOROrderMonth IN ([1], [2], [3])

) AS Pvt

ORDER BY ProductID;

表9-9                                                                   旋转后输出的内容

ProductID	Jan	Feb	Mar
1	300.00	100.00	100.00
2	100.00	NULL	100.00
3	NULL	200.00	100.00
4	NULL	NULL	100.00

上面的查询语句将按下面的步骤来获取表9-9所示的结果集：

·   PIVOT首先按属性值列之外的列（ProductID和OrderMonth）对输入表Sales.Orders进行分组汇总，类似执行下面的语句，得到一个如表9-10所示的中间结果集。

SELECT ProductID,

       OrderMonth,

       SUM(SubTotal) AS SubTotal

FROM (SELECT ProductID,MONTH(OrderDate) AS OrderMonth, SubTotal

      FROM Orders) AS O1

GROUP BY ProductID,OrderMonth;

表9-10                                                         Orders经分组汇总后的结果

ProductID	OrderMonth	SubTotal
1	1	300.00
1	2	100.00
1	3	100.00
2	1	100.00
2	3	100.00
3	2	200.00
3	3	100.00
4	3	100.00

·   PIVOT根据FOR OrderMonth IN指定的值1、2、3，首先在结果集中建立名为1、2、3的列，然后从表9-10所示的中间结果中取出SubTotal列中取出相符合的值，分别放置到1、2、3列中。此时得到的结果集的别名为pvt（见语句中AS pvt的指定）。结果集的内容如表9-11所示。

表9-11                                   使用FOR OrderMonth IN ([1], [2], [3])后得到的结果集

ProductID	1	2	3
1	300.00	100.00	100.00
2	100.00	NULL	100.00
3	NULL	200.00	100.00
4	NULL	NULL	100.00

·   最后根据SELECT ProductID, [1] AS Jan,[2] AS Feb, [3] AS Mar FROM的指定，从别名pvt结果集中检索数据，并分别将名为1、2、3的列在最终结果集中重新命名为Jan、Feb、Mar，得到表9-9所示的结果集。这里需要注意的是FROM的含义，其表示从经PIVOT关系运算符得到的pvt结果集中检索数据，而不是从Orders或派生表O1中检索数据。

在SQL Server2005之前，要进行行列转换比较烦琐，你需要考虑源表中行与结果集中行的关系，属性列中的每个唯一值在结果集中都需要一个列。像上面表9-7中的Orders表由于包含3个月份的数据，因此在SELECT列表中需要包含3个表达式，分别用于提取3个月份中的数据。下面语句的查询结果与表9-9相同，请读者自己分析下面的语句。

SELECT ProductID,

      SUM(CASE WHEN OrderMonth = 1 THEN SubTotal END) AS Jan,

      SUM(CASE WHEN OrderMonth = 2 THEN SubTotal END) AS Feb,

      SUM(CASE WHEN OrderMonth = 3 THEN SubTotal END) AS Mar

FROM (SELECT ProductID,

            MONTH(OrderDate) AS OrderMonth,

            SubTotal AS SubTotal

      FROMOrders) AS O1

GROUP BY ProductID;

9.4.2 UNPIVOT运算符

UNPIVOT与PIVOT执行几乎完全相反的操作，将列转换为行。但是，UNPIVOT并不完全是PIVOT的逆操作，由于在执行PIVOT过程中，数据已经被进行了分组汇总，所以使用UNPIVOT并不会重现原始表值表达式的结果。假设表9-9所示的结果集存储在一个名为MyPvt的表中，现在需要将列Jan、Feb和Mar转换到对应于相应产品ID的行值（即返回到表9-10所示的格式）。这意味着必须另外标识两个列，一个用于存储月份，一个用于存储销售额。为了便于理解，仍旧分别将这两个列命名为OrderMonth和SubTotal。

下面的语句首先创建MyPvt表，然后将查询数据插入到表中。

CREATE TABLE MyPvt

(

   ProductID int NOT NULL,

   Jan money,

   Feb money,

   Mar money

);

 

INSERT INTO MyPvt(ProductID, Jan, Feb, Mar)

SELECT ProductID,

       [1] AS Jan,

       [2] AS Feb,

       [3] AS Mar

FROM (SELECT ProductID,MONTH(OrderDate) AS OrderMonth, SubTotal

      FROM Orders) AS O1

PIVOT

(

   SUM(SubTotal)

   FOR OrderMonth IN ([1], [2], [3])

) AS Pvt

ORDER BY ProductID;

下面的语句执行UNPIVOT，将得到表9-12所示的查询结果。

SELECT ProductID,OrderMonth, SubTotal

FROM MyPvt

UNPIVOT

(

   SubTotal FOR OrderMonth IN (Jan, Feb, Mar)

)AS UnPvt;

表9-12                                                          UNPIVOT得到的查询结果

ProductID	OrderMonth	SubTotal
1	Jan	300.00
1	Feb	100.00
1	Mar	100.00
2	Jan	100.00
2	Mar	100.00
3	Feb	200.00
3	Mar	100.00
4	Mar	100.00

上面的语句将按下面的步骤获得输出结果集：

·   首先建立一个临时结果集的结构，该结构中包含MyPvt表中除IN (Jan, Feb, Mar)之外的列，以及SubTotalFOR OrderMonth中指定的属性值列（SubTotal）和属性列（OrderMonth）。

·   然后将在MyPvt中逐行检索数据，将表的列名称放入OrderMonth列中，将相应的值放入到SubTotal列中。

由于在PIVOT时为列指定了别名，所以在UNPIVOT后，OrderMonth列中的月份使用的是英文简称，而不是表9-10所示的格式。要得到表9-10所示的格式，可以在查询语句中使用CASE表达式来解决这个问题，参考下面的语句：

SELECT ProductID,

      CAST(CASE

           WHEN OrderMonth ='Jan' THEN '1'

           WHEN OrderMonth ='Feb' THEN '2'

           WHEN OrderMonth ='Mar' THEN '3'

           END AS int) AS OrderMonth,

      SubTotal

FROM MyPvt

UNPIVOT

(

   SubTotalFOR OrderMonth IN (Jan, Feb, Mar)

)AS UnPvt;

在SQL Server2005之前，则应当使用下面的语句：

SELECT * FROM

  (SELECTProductID,1 AS OrderMonth, Jan AS SubTotal

   FROMMyPvt

   UNION ALL

   SELECTProductID,2 AS OrderMonth, Feb

   FROMMyPvt

   UNION ALL

   SELECT ProductID,3 AS OrderMonth, Mar

   FROMMyPvt) AS O

WHERE SubTotal IS NOT NULL;

9.4.3 CASE表达式在9.4节我们介绍了使用PIVOT运算符进行表行列转换的方法，有效解决了表的数据存储和方便阅读的矛盾问题，本节将提供另外的一种转换方式。这是在做一个考试系统时遇到的问题，下面是是创建示例表的语句。表中存放着学生每次各科的考试成绩。

CREATE TABLE exams

(stu_name char(10) NOT NULL,

 exam_datedate NOT NULL,

 exam_subchar(10) NOT NULL,

 exam_scoreint);

INSERT INTO exams

VALUES ('张三', '2009-06-20', '语文', 90),

       ('张三', '2009-06-20', '数学', 95),

       ('张三', '2009-06-20', '英语', 100),

       ('张三', '2009-09-20', '语文', 85),

       ('张三', '2009-09-20', '数学', 90),

       ('张三', '2009-09-20', '英语', 98),

       ('李四', '2009-06-20', '语文', 80),

       ('李四', '2009-06-20', '数学', 85),

       ('李四', '2009-06-20', '英语', 90),

       ('李四', '2009-09-20', '语文', 75),

       ('李四', '2009-09-20', '数学', 80),

       ('李四', '2009-09-20', '英语', 88);

现在要获得如表12-23所示的格式，每个学生按考试日期在表中占一行。

表12-23                                                                     转换后结果

 

stu_name	exam_date	语文	数学	英语
张三	2009-06-20	90	95	100
张三	2009-09-20	85	90	98
李四	2009-06-20	80	85	90
李四	2009-09-20	75	80	88

在许多情况下，都可以使用CASE表达式将表的行转换为列，这是一个非常有用的技巧。参考下面的语句：

SELECT stu_name, exam_date,

       CASEWHEN exam_sub = '语文' THENexam_score

            ELSE NULL

       ENDAS 语文,

       CASEWHEN exam_sub = '数学' THENexam_score

           ELSE NULL

       ENDAS 数学,

       CASEWHEN exam_sub = '英语' THENexam_score

           ELSE NULL

       ENDAS 英语

FROM exams;      

上面语句将得到如表12-24所示的结果。

表12-24                                                     使用CASE表达式得到的结果

 

stu_name	exam_date	语文	数学	英语
张三	2009-06-20	90	NULL	NULL
张三	2009-06-20	NULL	95	NULL
张三	2009-06-20	NULL	NULL	100
张三	2009-09-20	85	NULL	NULL
张三	2009-09-20	NULL	90	NULL
张三	2009-09-20	NULL	NULL	98
李四	2009-06-20	80	NULL	NULL
李四	2009-06-20	NULL	85	NULL
李四	2009-06-20	NULL	NULL	90
李四	2009-09-20	75	NULL	NULL
李四	2009-09-20	NULL	80	NULL
李四	2009-09-20	NULL	NULL	88

由上表可以看出，只要按stu_name、exam_date分组计算最大值，就可以得到表12-23所要求的计算结果。参考下面的语句：

SELECT stu_name, exam_date,

      MAX(CASE WHEN exam_sub = '语文' THEN exam_score

               ELSE NULL

          END) AS 语文,

      MAX(CASE WHEN exam_sub = '数学' THEN exam_score

               ELSE NULL

          END) AS 数学,

      MAX(CASE WHEN exam_sub = '英语' THEN exam_score

               ELSE NULL

           END) AS 英语

FROM exams

GROUP BY stu_name, exam_date

ORDER BY stu_name, exam_date;

 

锋利的SQL-SQL Server的表旋转（行列转换）

• 收藏(2)
• 赞(67)
• 举报

分享到：

执行计划中cost计算方法
执行计划中cost计算方法概念： blevel：二元高度=索引高度-1 clustering_factor：集群因子，扫描index scan得出的要扫描的表中block数，clustering_factor select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name=&#039;IDX_T&#039;; LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR ----------- ----...

mysql 解压及赋权
mysql 解压及赋权解压mysql压缩版至某根目录注意ini文件内容配置  basedir = D:\mysql-5.6.17-winx64  datadir = D:\mysql-5.6.17-winx64  port = 3306  server_id = mssql default-character-set=utf8 cmd cd mysql bin 目录 mysqld install mysql net start mysql ------------------ 用户zs 库zsk 密码zsp mysql -u...

• MySQL触发器 trigger之for each row Feb 25, 2017
• Goldengate Checkpoint Table Is Corrupted Dec 10, 2016
• 朴素贝叶斯算法在垃圾邮件过滤中的应用 May 03, 2016
• 金融系列13《卡面标识》 Nov 12, 2016

• MySQL数据库临时文件究竟储存在哪里 Jul 16, 2016
• mysql如何插入多条数据？ Aug 21, 2023
• oracle_set运算符 Dec 10, 2016
• mysql、sqlserver、oracle三种数据库维护索引、外键、字段语法总结 Nov 27, 2016

•         php迷，一个php技术的分享社区，专属您自己的技术摘抄本、收藏夹。
• 在这里……

Tags

• 共同学习
• 发现
• 收藏
• 分享
• 共享
• 共鸣
• 专注
• 共同进步

About

    特别鸣谢     使用协议

    意见反馈     网站投稿

网站声明：本站所有视频，教程都由网友上传，站长收集和分享给大家学习使用，如由牵扯版权问题请联系站长邮箱  冀ICP备18013926号-2

Scroll