T-SQL逻辑处理之表运算符
时间：2011-03-11 来源：JianWang
在前两篇T-SQL查询处理详解, T-SQL查询处理详解 （续）中，我较为详细地介绍了在一般查询中忽略实际的优化执行计划的情况下的逻辑处理过程。接下来，我将从表运算符，连接查询，分析函数，子查询等方面入手，深入地探讨这些细节，欢迎大家一起讨论T-SQL开发中遇到的问题。废话不多说，开始了。
SQL SERVER 2008支持4种表运算符，JOIN，PIVOT，UNPIOVT，APPLY,。这四种运算符，JOIN大家肯定不陌生，但是后面的几个，估计就有不少人很少使用了。
一.PIVOT。
首先看这样的一条SQL查询。
SELECT EmployeeID,
SUM(CASE WHEN theyear = 2006 THEN themoney END) AS [2006],
SUM(CASE WHEN theyear = 2007 THEN themoney END) AS [2007],
SUM(CASE WHEN theyear = 2008 THEN themoney END) AS [2008],
SUM(CASE WHEN theyear = 2009 THEN themoney END) AS [2009],
SUM(CASE WHEN theyear = 2010 THEN themoney END) AS [2010],
SUM(CASE WHEN theyear = 2011 THEN themoney END) AS [2011]
FROM (SELECT EmployeeID, YEAR(Orderdate) AS theyear, themoney
FROM dbo.Order) AS O
GROUP BY EmployeeID; 这种行转列的需求，在实际情况中特别常见，尤其是在提取年度或者月度报表的数据时，基本少不了这种结果集。看看上面的这条查询，是不是觉得不是很好。不好在哪里呢？有两点，1.代码量大，而且重复代码太多，碰到很多行要转成列的情况，就非常麻烦了。在项目里，经常碰到做月度报表，需要把12个月的数据利用这种写法转成列，就让人吐血了；2.执行效率不高。 这时，我向您介绍T-SQL中特有的表运算符，PIVOT。Pivot运算符用于在列和行之间对数据进行旋转或透视转换，同时执行聚合运算。对于上面的需求，用PIVOT可以如下实现相同的功能: SELECT * FROM (SELECT EmployeeID, YEAR(Orderdate) AS theyear, themoney
FROM dbo.Order) AS O
PIVOT(SUM(themoney) FOR theyear IN([2006],[2007],[2008],[2009],[2010],[2011])) AS P; 怎么样，是不是简洁多了？ 来简要分析一下PIVOT的执行过程。PIVOT运算符用一个名为O的表表达式作为它的左输入，每个订单占一行，包含EmployeeID和theyear和themoney。 PIVOT操作涉及到一下三个逻辑阶段。 1.分组。来看看PIVOT子句，我们发现，PIVOT运算符引用了表O（给表取的别名）的两个列作为输入参数，分别是themoney和theyear。在这个阶段，会隐式对表O中的行进行分组，分组依据是根据那些没有作为pivot输入参数的所有列。所以，就相当于有一个隐藏的group by EmployeeID一样，最后结果集就是每个EmployeeID作为一个组。 2.扩展。这个阶段是把原始列的值扩展到他们相应的目标列中，在逻辑上，相当于为IN子句中指定的每个目标列使用以下case表达式： CASE WHEN <spreading_col> = <target_col_element> THEN <expression> END. 注意一点，不带ELSE的CASE子句相当于包含了一个隐式的ELSE NULL。 3.聚合。这个阶段就是对每个CASE表达式应用指定的聚合函数，对于上边的查询，就是SUM()，并生成结果列。 对于PIVOT的更多的应用，有机会再深入了解。 二.UNPIVOT。 很明显，UN这个前缀表明了，它做的操作是跟PIVOT相反的，即列转行。UNPIVOT操作涉及到以下三个逻辑处理阶段。 1，生成副本 2，提取元素 3，删除带有NULL的行 看看这个MSDN上的例子。 CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO 简单对上边的查询作一个分析。UNPIVOT的输入是左表表达式P，第一步，先为P中的行生成多个副本，在UNPIVOT中出现的每一列，都会生成一个副本。因为这里的IN子句有5个列名称，所以要为每个来源行生成5个副本。结果得到的虚拟表中将新增一个列，用来以字符串格式保存来源列的名称（for和IN之间的，上面例子是Employee ）。第二步，根据新增的那一列中的值从来源列中提取出与列名对应的行。第三步，删除掉结果列值为null的行，完成这个查询。 三.APPLY。 APPLY运算符是把右表表达式应用与左输入中的每一行。对于左表中的每一行，都要计算一次右边输入的表达式。 APPLY 有两种形式：CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过右表表达式生成结果集的行。OUTER
APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中右表表达式生成的列中的值为 NULL。（外部表：左边表，内部表：右表表达式） 以下示例查询为每个客户返回具有最大订单ID的两个订单： SELECT C.customerid, C.city, A.orderid
FROM dbo.Customers AS C
CROSS APPLY
(SELECT TOP (2) O.orderid, O.customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC) AS A; 如果将CROSS APPLY换成了OUTER APPLY，则会把不满足右表表达式的行添加进来，并给内部表的列设为NULL。 欢迎大家一起探讨T-SQL开发，并提出问题，一起讨论。 参考文献：《Microsoft SQL Server 2008 技术内幕：T-SQL查询》
SUM(CASE WHEN theyear = 2006 THEN themoney END) AS [2006],
SUM(CASE WHEN theyear = 2007 THEN themoney END) AS [2007],
SUM(CASE WHEN theyear = 2008 THEN themoney END) AS [2008],
SUM(CASE WHEN theyear = 2009 THEN themoney END) AS [2009],
SUM(CASE WHEN theyear = 2010 THEN themoney END) AS [2010],
SUM(CASE WHEN theyear = 2011 THEN themoney END) AS [2011]
FROM (SELECT EmployeeID, YEAR(Orderdate) AS theyear, themoney
FROM dbo.Order) AS O
GROUP BY EmployeeID; 这种行转列的需求，在实际情况中特别常见，尤其是在提取年度或者月度报表的数据时，基本少不了这种结果集。看看上面的这条查询，是不是觉得不是很好。不好在哪里呢？有两点，1.代码量大，而且重复代码太多，碰到很多行要转成列的情况，就非常麻烦了。在项目里，经常碰到做月度报表，需要把12个月的数据利用这种写法转成列，就让人吐血了；2.执行效率不高。 这时，我向您介绍T-SQL中特有的表运算符，PIVOT。Pivot运算符用于在列和行之间对数据进行旋转或透视转换，同时执行聚合运算。对于上面的需求，用PIVOT可以如下实现相同的功能: SELECT * FROM (SELECT EmployeeID, YEAR(Orderdate) AS theyear, themoney
FROM dbo.Order) AS O
PIVOT(SUM(themoney) FOR theyear IN([2006],[2007],[2008],[2009],[2010],[2011])) AS P; 怎么样，是不是简洁多了？ 来简要分析一下PIVOT的执行过程。PIVOT运算符用一个名为O的表表达式作为它的左输入，每个订单占一行，包含EmployeeID和theyear和themoney。 PIVOT操作涉及到一下三个逻辑阶段。 1.分组。来看看PIVOT子句，我们发现，PIVOT运算符引用了表O（给表取的别名）的两个列作为输入参数，分别是themoney和theyear。在这个阶段，会隐式对表O中的行进行分组，分组依据是根据那些没有作为pivot输入参数的所有列。所以，就相当于有一个隐藏的group by EmployeeID一样，最后结果集就是每个EmployeeID作为一个组。 2.扩展。这个阶段是把原始列的值扩展到他们相应的目标列中，在逻辑上，相当于为IN子句中指定的每个目标列使用以下case表达式： CASE WHEN <spreading_col> = <target_col_element> THEN <expression> END. 注意一点，不带ELSE的CASE子句相当于包含了一个隐式的ELSE NULL。 3.聚合。这个阶段就是对每个CASE表达式应用指定的聚合函数，对于上边的查询，就是SUM()，并生成结果列。 对于PIVOT的更多的应用，有机会再深入了解。 二.UNPIVOT。 很明显，UN这个前缀表明了，它做的操作是跟PIVOT相反的，即列转行。UNPIVOT操作涉及到以下三个逻辑处理阶段。 1，生成副本 2，提取元素 3，删除带有NULL的行 看看这个MSDN上的例子。 CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO 简单对上边的查询作一个分析。UNPIVOT的输入是左表表达式P，第一步，先为P中的行生成多个副本，在UNPIVOT中出现的每一列，都会生成一个副本。因为这里的IN子句有5个列名称，所以要为每个来源行生成5个副本。结果得到的虚拟表中将新增一个列，用来以字符串格式保存来源列的名称（for和IN之间的，上面例子是Employee ）。第二步，根据新增的那一列中的值从来源列中提取出与列名对应的行。第三步，删除掉结果列值为null的行，完成这个查询。 三.APPLY。 APPLY运算符是把右表表达式应用与左输入中的每一行。对于左表中的每一行，都要计算一次右边输入的表达式。 APPLY 有两种形式：CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过右表表达式生成结果集的行。OUTER
APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中右表表达式生成的列中的值为 NULL。（外部表：左边表，内部表：右表表达式） 以下示例查询为每个客户返回具有最大订单ID的两个订单： SELECT C.customerid, C.city, A.orderid
FROM dbo.Customers AS C
CROSS APPLY
(SELECT TOP (2) O.orderid, O.customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC) AS A; 如果将CROSS APPLY换成了OUTER APPLY，则会把不满足右表表达式的行添加进来，并给内部表的列设为NULL。 欢迎大家一起探讨T-SQL开发，并提出问题，一起讨论。 参考文献：《Microsoft SQL Server 2008 技术内幕：T-SQL查询》
相关阅读 更多 +
