如何从单行提取成多行（TSQL）？

Outline：

• 问题产生背景
• 交叉连接
• UNION操作
• WITH子句
• 性能比较
• 抛砖引玉（欢迎指教！）

问题产生背景

从一个仅有数额指标的交易表中创建一个查询，这个查询用于会计日志条目。因此如果一个日志条目应该有存款和借款，但是其他的值应该是相同的，因此我想通过SQL查询从一行提取2行记录。听起来很模糊？我Google之后发现没有任何结果，所以我就写了这篇文章。为了简化，这里举个例子。我有一张表叫做SampleTable，它包含如下记录：

ID	FirstName	LastName
1	Anna	Gates
2	John	Doe
3	Joe	Bloggs
4	Raj	Kumar

现在你的任务是为每个记录创建3行，因此它将显示如下：

ID	FirstName	LastName	ItemNumber	ItemDescription
1	Anna	Gates	1	Item 1
1	Anna	Gates	2	Item 2
1	Anna	Gates	3	Item 3
2	John	Doe	1	Item 1
2	John	Doe	2	Item 2
2	John	Doe	3	Item 3
3	Joe	Bloggs	1	Item 1
3	Joe	Bloggs	2	Item 2
3	Joe	Bloggs	3	Item 3
4	Raj	Kumar	1	Item 1
4	Raj	Kumar	2	Item 2
4	Raj	Kumar	3	Item 3

现在，你如何实现它呢？有许多种方法，但是我们要找出哪个方法查询效率最高。有下面是那种方法：

• 交叉连接
• UNION查询
• WITH查询

1、交叉连接

为了激活大家对交叉连接沉睡的记忆，首先介绍下什么是交叉连接。所谓交叉连接，就是两个表的笛卡尔积的另一称谓。交叉连接为将第一张表的每一行与第二张表的每一行组合产生一新的元组。设两张表R、S分别有k1、k2条记录，每条记录的列数分别为m、n，则交叉连接的结果元组数为k1*k2，每个元组的列数为m+n（前面m列是R的，后面n列是S的）。当然这是在没有where条件的情况下，如果加了where添加可能会过滤掉一部分不符合条件的记录。

所以上面的结果可以看成下面两张表的交叉连接产生的：

因此可以用如下SQL语句：

SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, Extender.ItemNumber, Extender.ItemDescription
FROM SampleTable CROSS JOIN
(SELECT 1 AS ItemNumber, 'Item 1' AS ItemDescription
UNION ALL
SELECT 2 AS ItemNumber, 'Item 2' AS ItemDescription
UNION ALL
SELECT 3 AS ItemNumber, 'Item 3' AS ItemDescription) AS Extender;

本来是打算对表2构建一张临时表，但考虑到SQL Server与Oracle构建临时表是有差异的，考虑到这个我就用上面这种方式（UNION ALL，集合查询）。

2、UNION操作

UNION是集合操作中的一种，SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。注意，参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

下面是实现代码：

SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 1 AS ItemNumber, 'Item 1' AS ItemDescription
FROM SampleTable
UNION ALL
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 2 AS ItemNumber, 'Item 2' AS ItemDescription
FROM SampleTable
UNION ALL
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 3 AS ItemNumber, 'Item 3' AS ItemDescription
FROM SampleTable;

3、WITH子句

WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

其实with子句提供定义一个临时视图的方法，这个定义只对with子句出现的那条查询有效。换就话说，就是把查询结果放入一个临时表，然后通过查询语句从临时表查询结果。

如果你对with子句还是不熟悉理解的话，看下面的例子。下面的查询是：查询具有最大余额的账户的语句，如果具有同样最大查询的账户有很多，他们都会被选择。即假设如果最大余额是10000的账户有3个，则这三个账户都会显示出来。
with max-balance(value) as
        select max(balance)
        from account
select account-number
from account,max-balance
where account.balance=max-balance.value
我想看到这，with子句大家都理解了。下面是上面问题的实现代码：

WITH ExtendedTable(ID, FirstName, LastName) AS
(SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName
FROM SampleTable)
SELECT *, 1 AS ItemNumber, 'Item 1' AS ItemDescription from ExtendedTable
UNION ALL
SELECT *, 2 AS ItemNumber, 'Item 2' AS ItemDescription from ExtendedTable
UNION ALL
SELECT *, 3 AS ItemNumber, 'Item 3' AS ItemDescription from ExtendedTable

性能比较

上面三种方法都可以得到相同的结果，那到底它们的性能到底如何呢？下面我们把这三种方法的代码放到同一个查询中执行，如下：
-----------------------------------------
--方法一、交叉连接
-----------------------------------------
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, Extender.ItemNumber, Extender.ItemDescription
FROM SampleTable CROSS JOIN
(SELECT 1 AS ItemNumber, 'Item 1' AS ItemDescription
UNION ALL
SELECT 2 AS ItemNumber, 'Item 2' AS ItemDescription
UNION ALL
SELECT 3 AS ItemNumber, 'Item 3' AS ItemDescription) AS Extender;

-----------------------------------------
--方法二、UNION操作
-----------------------------------------
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 1 AS ItemNumber, 'Item 1' AS ItemDescription
FROM SampleTable
UNION ALL
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 2 AS ItemNumber, 'Item 2' AS ItemDescription
FROM SampleTable
UNION ALL
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, 3 AS ItemNumber, 'Item 3' AS ItemDescription
FROM SampleTable;

-----------------------------------------
--方法三、WITH子句
-----------------------------------------
WITH ExtendedTable(ID, FirstName, LastName) AS
(SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName
FROM SampleTable)
SELECT *, 1 AS ItemNumber, 'Item 1' AS ItemDescription from ExtendedTable
UNION ALL
SELECT *, 2 AS ItemNumber, 'Item 2' AS ItemDescription from ExtendedTable
UNION ALL
SELECT *, 3 AS ItemNumber, 'Item 3' AS ItemDescription from ExtendedTable

执行查询时选中包括实际的执行计划（在SQL Server的查询菜单下面），得到执行计划如下图所示（由于图太大分三张贴上）。

方法一：

方法二：

方法三：

从图中我们可以清楚地看到方法一得开销仅占15%，而方法二、三相同都占43%。由此可见交叉连接的性能最好，而union操作与with子句性能相对较低。

抛砖引玉

ps.不知道您有没有看到这里，可能很多看官还没看到这节就把网页给关了（⊙﹏⊙b汗）。如果您看到这里了，你能说出造成这个性能差异的原因吗？欢迎大家回帖，包括拍砖。

我指出其中一点：交叉连接和with子句一样都是用构建一张临时表与SampleTable做连接，但是他们的性能差异源于交叉连接时执行select语句时做了聚簇索引。（just maybe，I'am not sure！）

另外推荐一篇讲with ties的文章：偶遇with ties