第十周翻译
t - sql先进水平的阶梯1:介绍先进的t - sql使用交叉连接
通过格雷戈里·拉森,2016/02/19(第一次出版:2014/12/17)
该系列
本文是楼梯系列的一部分:先进的t - sql的阶梯
这个楼梯将包含一个系列文章将扩大的t - sql的基础上你之前学到的两个t - sql楼梯,楼梯以外的t - sql DML和t - sql基础知识。 这个楼梯应该帮助读者准备通过微软认证考试70 - 461:查询Microsoft SQL Server 2012。
这是一个新的楼梯系列的第一篇文章将探索更高级的功能的SQL(TSQL)办理。 这楼梯将包含一个系列文章,将扩大TSQL基础,您学习了在前两个TSQL楼梯:
这种“先进处理SQL”楼梯将涵盖以下TSQL主题:
- 使用交叉连接操作符
- 使用应用操作符
- 理解公共表表达式(CTE)
- 记录级别处理使用transact - sql游标
- 将使用主数据的支持
- 将使用透视列进行
- 订购您的数据使用排序的功能
- 处理日期和时间函数
- 理解变化的条款
这楼梯的读者应该已经很好地理解如何查询、更新、插入和删除SQL Server数据表。 另外他们应该的工作知识的方法可用于控制流的TSQL代码,也能够测试和操作数据。
这个楼梯应该帮助读者准备通过微软认证考试70 - 461:查询Microsoft SQL Server 2012。
这个新的楼梯系列的第一部分我将讨论交叉连接操作符。
介绍交叉连接操作符
交叉连接操作符可以用来把所有记录在一个数据集的所有记录在另一个数据集。通过使用两个集合之间的交叉连接操作符记录您正在创建所谓的笛卡儿积。
这里是一个简单的例子使用交叉连接操作符加入a和B两个表:
选择*从一个交叉 加入B
注意,当使用一个交叉连接操作符连接两个表像没有加入条款执行时将会用到一种内在和外在的两个表之间的连接操作。
你需要意识到使用交叉连接可以产生大量纪录。探索这种行为让我们看看两种不同的结果集是多大的例子从一个交叉连接操作。 第一个示例假设您是交叉连接两个表,表有10行和表B有3行。 resulti的交叉连接将10 * 3或者30行。 对于第二个示例假定表有1000万行和表B 300万行。 多少行将在a和B之间的交叉连接结果集表吗? 这将是一个巨大的30000000000000行。 很多行和它将SQL Server大量的时间和大量的资源来创建结果集。因此,你需要小心使用交叉连接操作符在大型记录集。
稍微让我们来看看使用交叉连接操作符通过研究几个例子。
使用交叉连接的基本示例
第一夫妇的例子,我们将加入两个示例表。 清单1中的代码将用于创建这两个示例表。 确保你运行这些脚本在一个数据库,而不是在用户数据主。
创建 表产品(IDint,ProductNamevarchar(One hundred.),成本钱);
创建 表SalesItem(IDint,SalesDatedatetime,ProductIDint,数量int,TotalSalesAmt钱);
插入 成产品值 (1,“部件”,21.99),
(2,“装置”,5.38),
(3,“Watchamacallit”,1.96);
插入 成SalesItem值 (1,“2014-10-1”,1,1,21.99),
(2,“2014-10-2”,3,1,1.96),
(3,“2014-10-3”,3,10,19.60),
(4,“2014-10-3”,1,2,43.98),
(5,“2014-10-3”,1,2,43.98);
清单1:示例表进行交叉连接
第一交叉连接的例子中,我将清单2中的代码运行。
选择*从产品交叉 加入SalesItem;
清单2:简单的交叉连接的例子
当我运行清单2中的代码在一个SQL Server Management Studio窗口中,用我的会话设置输出文本我得到输出报告中的结果1:
ID ProductName成本ID SalesDate ProductID数量TotalSalesAmt
推荐- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1小部件21.99 21.99 1 2014-10-01 00:00:00.000 1 1
1小部件21.99 2 2014-10-02 00:00:00.000 3 1 1.96
1小部件21.99 3 2014-10-03 00:00:00.000 3 10 19.60
21.99 4 2014-10-03 00:00:00.000 1 2 43.98 1小部件
21.99 5 2014-10-03 00:00:00.000 1 2 43.98 1小部件
2装置5.38 21.99 1 2014-10-01 00:00:00.000 1 1
装置5.38 2 2014-10-02 00:00:00.000 3 1至5.38点
2装置5.38 3 2014-10-03 00:00:00.000 3 10 19.60
5.38装置4 2014-10-03 00:00:00.000 1 2至5.38点
5.38装置5 2014-10-03 00:00:00.000 1 2至5.38点
3 Watchamacallit 1.96 21.99 1 2014-10-01 00:00:00.000 1 1
3 Watchamacallit 1.96 2 2014-10-02 00:00:00.000 3 1 1.96
3 Watchamacallit 1.96 3 2014-10-03 00:00:00.000 3 10 19.60
3 Watchamacallit 1.96 4 2014-10-03 00:00:00.000 1 2 43.98
3 Watchamacallit 1.96 5 2014-10-03 00:00:00.000 1 2 43.98
报告1:当运行清单2所示的结果
如果你回顾报告1中的结果可以看到,有15个不同的记录。 这些前5条记录包含的列值的第一行产品表与5中不同的行SalesItem表。 这同样适用于2秒和3行产品表。 总返回的行数的行数产品表时间的行数SalesItem表,这是15行。
创建一个笛卡儿积的一个原因是生成测试数据很有用。 假设我想产生许多不同的产品使用在我的日期产品和SalesItem表。 我可以使用一个交叉连接,如清单3:我做了
选择 ROW_NUMBER() 在(订单 通过ProductNameDESC) 作为ID,产品。ProductName+投(SalesItem。ID作为 varchar(2)) 作为ProductName,
(产品。成本/SalesItem。ID)*One hundred. 作为成本从产品交叉 加入SalesItem;
清单3:简单的交叉连接的例子
当我运行清单3中的代码报告2中我得到的输出。
ID ProductName成本
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 Widget1 2199.00
2 Widget2 1099.50
3 Widget3 733.00
4 Widget4 549.75
5 Widget5 439.80
6 Watchamacallit1 196.00
7 Watchamacallit2 98.00
8 Watchamacallit3 65.33
9 Watchamacallit4 49.00
10 Watchamacallit5 39.20
11 Thingamajig1 538.00
12 Thingamajig2 269.00
13 Thingamajig3 179.33
14 Thingamajig4 134.50
15 Thingamajig5 107.60
报告2:当运行清单3的结果
你可以看到通过回顾我的代码在清单3中我生成一个包含数据的行数类似于在我的数据产品表。 通过使用函数ROW_NUMBER我能够生成一个唯一的ID列在每一行。 另外我用了ID列从我SalesItem表创建独特ProductName,成本列值。 的行数等于生产的行数产品表时间的行数SalesItem表。
到目前为止只有一节中的示例进行了交叉连接两个表。 您可以使用交叉连接操作符执行交叉跨多个表的连接操作。 清单4中的示例创建一个笛卡儿积跨三个表。
选择*从sys。表交叉 加入sys。对象交叉 加入sys。sysusers;
清单4:使用交叉连接操作符创建一个笛卡儿积的三个表
运行清单4的输出有两种不同的CROSS_JOIN操作。 创建的笛卡儿积的这段代码将生成一个结果集将行总数等于中的行数sys.tables倍的行数sys.objects倍的行数sys.sysusers。
当一个交叉连接执行像一个内连接
在前一节中我提到的,当你使用一个交叉连接操作符,它将产生一个笛卡儿积。 这是不正确的。 当你使用WHERE子句的加入限制表参与交叉连接操作SQL Server并不创建一个笛卡儿积。 相反,它功能像一个正常的连接操作。 为了演示这种行为,查看清单5中的代码。
选择*从产品页交叉 加入SalesItem年代在哪里P。ID=年代。ProductID;
选择*从产品页内心的 加入SalesItem年代在P。ID=年代。ProductID;
清单5:两个SELECT语句是等价的。
清单5中的代码包含两个SELECT语句。 第一个SELECT语句使用交叉连接操作符,然后使用WHERE子句定义如何加入参与交叉连接的两个表的操作。 第二个SELECT语句使用正常的内连接操作符有一个条款加入两个表。 SQL服务器的查询优化器是足够聪明知道清单5中的第一个SELECT语句可以重写为一个内连接。 优化器知道它可以重写查询当交叉连接操作结合WHERE子句中使用,它提供了一个连接谓词所涉及的两个表之间的交叉连接。 因此SQL服务器引擎生成相同的清单5中SELECT语句的执行计划。 当你不提供一个约束在SQL Server不知道如何加入到两个表中涉及交叉连接操作这两组之间创建一个笛卡儿积的交叉连接操作。
使用交叉连接找到产品不卖
前面的小节中的例子来帮助你理解交叉连接操作符和如何使用它。 使用交叉连接操作符的力量之一是使用它来帮助找到物品在一个表,另一个表中没有匹配的记录。 例如假设我想报告总数量和总销售额ProductName在我Product表为每个日期的任何一个我的产品出售的物品。 因为在每一个我的例子产品的名字不是每天都有出售,出售我的报告要求的意思是我需要显示0,0美元的销售总额的数量对于那些尚未售出的产品在给定的一天。 这就是交叉连接操作符与左外连接操作将帮助我识别那些没有的物品卖给的一天。 的代码在清单6中可以找到满足这些报告要求:
选择S1。SalesDate,ProductName,ISNULL(总和(S2。数量),0) 作为TotalQty,ISNULL(总和(S2。TotalSalesAmt),0) 作为TotalSales从产品页交叉 加入
(
选择 截然不同的SalesDate从SalesItem)S1左 外 加入SalesItem S2在P。ID=S2。ProductID和S1。SalesDate=S2。SalesDate集团 通过S1。SalesDate,P。ProductName订单 通过S1。SalesDate;
清单6:使用交叉连接发现产品不卖
让我陪你走过这段代码。 我创建了一个查询,所有不同的选择SalesDate值。 这个子查询的日期给我所有出售。 然后我与我的交叉连接产品表。 这让我创建一个笛卡儿积之间SalesDate和每个产品行。 返回的集交叉连接会有价值我需要在最后的结果集除了之和数量和TotalSalesAmt每个产品出售。 这些汇总值我执行左外连接SalesItem表加入我创建的笛卡儿积的交叉连接操作。 我完成了这个连接的基础上ProductID和SalesDate列。 通过使用每一行左外部连接在我的笛卡儿积将返回,如果有一个匹配SalesDate记录ProductID和SalesDate,的问泰和TotalSalesAmt值将与适当的行。 这个查询的最后一件事做的是使用GROUP BY子句总结数量和TotalSalesAmount基于SalesDate和ProductName。
性能考虑
交叉连接操作符,产生一个笛卡儿积有一些性能方面需要考虑。 因为SQL引擎需要加入每一行与每一行在另一组一组的结果集可以相当大。 如果我做一个交叉连接与另一个表,一个表有1000000行100000行我的结果集将有1000000 X 100000行,或100000000000行。 这是一个很大的结果集,并将SQL Server大量时间来创建它。
交叉连接操作符可以是一个伟大的解决方案确定一个结果集在所有可能的组合的两套,像所有的所有客户的销售每个月,即使几个月一些客户没有销售。 使用交叉连接操作符时,你应该尽量减少集的大小被交叉加入如果你想优化性能。 例如,假设我有一个表,包含2个月的销售数据。 如果我想产生一份报告,显示了没有任何的客户销售一个月,然后确定一个月的天数可以彻底改变我的查询的性能。 来演示这个让我先创建一组1000年销售记录客户两个月时间。 我将使用清单7中的代码。
创建 表Cust(Idint,CustNamevarchar(20.));
创建 表销售(Idint 身份
,CustIDint
,SaleDate日期,SalesAmt钱);
集NOCOUNT在;
声明@Iint = 0;
声明@Date日期;
而@I< 1000年
开始
集@I=@I+ 1;
集@Date= 返回(毫米, 2, “2014-11-01”);
插入 成Cust值 (@I,
“客户#” + 正确的(投(@I+ 100000 作为 varchar(6)),5));
而@Date< “2014-11-01”
开始
如果@I%7 > 0
插入 成销售(CustID,SaleDate,SalesAmt)
值 (@I,@Date, 10.00);
集@Date= 返回(DD, 1,@Date);
结束
结束
清单7:TSQL为性能测试创建示例数据
清单7中的代码创建了2个月的数据为1000个不同的客户。 这段代码添加没有销售数据每7日客户。 这段代码产生1000Cust表记录和52338销售表记录。
演示如何使用交叉连接操作符执行不同的大小取决于集用于交叉连接的输入集让我运行清单8和清单9中的代码。 对于每个测试我将记录的时间返回结果。
选择 转换(字符(6),S1。SaleDate,112年) 作为SalesMonth,C。CustName,ISNULL(总和(S2。SalesAmt),0) 作为TotalSales从Cust C交叉 加入
(
选择SaleDate从销售) 作为S1左 外 加入销售S2在C。ID=S2。CustID和S1。SaleDate=S2。SaleDate集团 通过 转换(字符(6),S1。SaleDate,112年),C。CustName有ISNULL(总和(S2。SalesAmt),0) = 0
订单 通过 转换(字符(6),S1。SaleDate,112年),C。CustName
清单8:交叉连接对所有销售记录
选择 转换(字符(6),S1。SaleDate,112年) 作为SalesMonth,C。CustName,ISNULL(总和(S2。SalesAmt),0) 作为TotalSales从Cust C交叉 加入
(
选择 截然不同的SaleDate从销售) 作为S1左 外 加入销售S2在C。ID=S2。CustID和S1。SaleDate=S2。SaleDate集团 通过 转换(字符(6),S1。SaleDate,112年),C。CustName有ISNULL(总和(S2。SalesAmt),0) = 0
订单 通过 转换(字符(6),S1。SaleDate,112年),C。CustName
清单9:交叉连接在不同的列表的销售日期
在清单8中交叉连接操作符连接1000Cust 记录有52338Sa莱斯记录产生一组记录的52338000行,然后用来确定零销售的客户一个月。 在清单9中,我改变了我的选择标准销售表只返回一个不同的组SalesDate值。 这只不同产生61种不同SalesDate值的结果清单9中的交叉连接操作只生产61000条记录。 通过减少交叉连接操作我查询的结果集在清单9中跑不到1秒,而清单8中的代码在19秒在我的机器上运行。 这个性能差异的主要原因是大量的记录SQL Server需要为每个查询过程执行不同的操作。 如果你观察执行计划的清单,你会发现计划略有不同。 但如果你看看嵌套循环产生的估计数量的记录(内连接)操作,右边的图形化计划,您将看到,清单8估计52338000条记录,而清单9中的相同操作只估计61000条记录。 这个大纪录清单8的查询计划生成从十字架上加入嵌套循环操作然后通过几个额外的操作。 因为所有这些操作在清单8中必须对5200万条记录。 清单8是大大低于清单9所示。
正如你所看到的记录的数量用于交叉连接操作可以显著影响查询运行的时间长度。 因此如果你可以编写查询记录的数量降到最低参与交叉连接的操作,您的查询将执行更有效。
结论
交叉连接操作符之间产生一个笛卡儿积的两个记录集。 这个操作符是有用的在帮助确定项目在一个表,另一个表中没有匹配的记录。 应注意减少记录集的大小与交叉连接操作符一起使用。 确保结果集的交叉连接尽可能小,您将确保您的代码尽可能快地运行。
问题和答案
在本节中,您可以检查你懂了如何使用交叉连接操作符通过回答下列问题。
问题1:
交叉连接操作符创建一个结果集通过匹配两个记录集基于on子句中指定的列。 (真或假)?
- 真正的
- 假
问题2:
该公式可以用来识别将返回的行数从一个无约束两个表A和B之间的交叉连接,当表A和B包含重复的行吗?
- 在表的行数乘以B表的行数
- 在表的行数乘以B表独特的行数
- 在表一个独特的行数乘以B表的行数
- 在表一个独特的行数乘以B表独特的行数
问题3:
哪种方法提供了最好的机会在减少笛卡儿积的大小产生的交叉连接操作吗?
- 确保两组参加尽可能多的行
- 确保两组参加尽可能少的一样行
- 确保将左边的交叉连接操作尽可能少的行
- 确保将正确的交叉连接的操作尽可能少的行
答案:
问题1:
正确的答案是b。交叉连接操作符不使用一个条款来执行交叉连接操作。 加入一个表中的每一行,每一行在其他表中。 交叉连接创建一个笛卡儿积连接两套。
问题2:
正确的答案是A,b,c和d是不正确的,因为如果有重复的行表A或b复制的每一行中加入的笛卡儿积在创建交叉连接操作。
问题3:
正确的答案是b。通过减少大小的两组参与交叉连接操作最小化的最后一组的大小由交叉中操作。 c和d也有助于减少最后一组的大小产生的交叉连接操作,但不像确保两组最优参与交叉连接的操作可能有最少的行。
