如何从单行提取成多行(TSQL)?
来源:互联网 发布:设计java线程池原理 编辑:程序博客网 时间:2024/05/09 04:01
如何从单行提取成多行(TSQL)?
2010-05-05 22:39 by 吴秦, 2628 阅读, 12 评论, 收藏, 编辑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添加可能会过滤掉一部分不符合条件的记录。
所以上面的结果可以看成下面两张表的交叉连接产生的:
交叉连接
2、UNION操作
UNION是集合操作中的一种,SELECT语句的查询结果是元组的集合,所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。注意,参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同。
下面是实现代码:
UNION操作
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子句
性能比较
上面三种方法都可以得到相同的结果,那到底它们的性能到底如何呢?下面我们把这三种方法的代码放到同一个查询中执行,如下:
-----------------------------------------
--方法一、交叉连接
-----------------------------------------
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
- 如何从单行提取成多行(TSQL)?
- 如何从单行提取成多行(TSQL)?
- 吴秦 如何从单行提取成多行(TSQL)?
- 如何提取音频从F4V
- 如何从另一个PPT中提取母版(WPS)
- 如何优化SQL Server TSQL
- 如何从数据库中提取文件?
- 如何从XML文件提取schema
- 如何从字符串中提取数值
- 如何从ipa提取图片资源
- 如何从PDF文件中提取矢量图
- 如何从TCP中提取HTTP报文
- Java如何从字符串中提取数字
- TSQL Regular Expression Workbench(TSQL的正则表达式)
- 利用cvGetRow()提取图像单行/列数据
- 高手指点(各位大侠,请教如何从图片中提取图片中的文字?)
- 如何从 JAR 和 zip 档案文件中提取 Java 资源
- 如何从doc文件里提取纯文本
- NXP TPMS LF PROTOCL GENERATER_TEST BENCH
- 根据IP获取天气预报信息29种样式
- D小P编程练习--求温度的中值和平均值
- android获取网络图片
- 【eoe 特刊】Windows 8 第一期 -- C#基本语法---sqlite数据库的使用---ListView 滚动加载 ---等
- 如何从单行提取成多行(TSQL)?
- 客户物料信息数据的批量导入(VD51) .
- 2.设计包含min函数的栈
- 观察者模式(监听器模式
- strut2 学习之验证框架一介绍
- 旋转翻转UIImage 不是UIImageView 适用于源图像的处理,例如截图后旋转
- 高斯消元法,高斯约旦消元法
- android学习1
- K-Means 算法