阶梯到高级T-SQL 1级:高级T-SQL介绍交叉连接。
这篇文章是楼梯系列的一部分:楼梯到高级T-SQL的系列文章。本文将包含一系列文章,这些文章将扩展您在前面两个T-SQL楼梯、楼梯到T-SQLDML和T-SQL基础上学习的基础。此阶梯应帮助读者为通过microsoft认证考试(70-461:查询microsoft sql server 2012)做好准备。
这是一个新的楼梯系列文章中的第一篇文章,它将探讨Transact-SQL(TSQL)的更高级的特性。这个楼梯将包含一系列文章,将在TSQL基础上扩展,在两个TSQL楼梯上学习。
通向T-SQL DML的阶梯
通向T-SQL的阶梯:超出基础
此“高级事务SQL”楼梯将涵盖以下TSQL主题:
·“使用交叉连接运算符”使用Apply运算符。
·使用Transact-SQL游标理解公共表表达式(CTE)的记录级处理。
·使用枢轴将数据转到它的一侧。使用UNPIVOT将列转换为行。
·使用排序函数对数据进行排序。
·管理日期和时间的功能。
·对超临界顶变化的认识 。
这个阶梯的读者应该已经很好地了解了如何从SQLServer表中查询、更新、插入和删除数据。另外,他们应该对可以用来控制TSQL代码流的方法有一定的了解,并且能够测试和操作数据 。
这个阶梯应该帮助读者为通过Microsofl证书考试70-4B1:查询Mi.rosofl SQLServer 2012的第一批分期付款做好准备。
如果是这样,我将讨论交叉连接算子。
交叉连接算子的介绍
交叉连接操作符可用于将一个数据集中的所有记录组合到另一个数据集中的所有记录。通过在两组记录之间使用交叉连接操作符,您正在创建所谓的笛卡儿生产。
这里是一个简单的例子,说明了两个表A和B的使用交叉连接操作符。
SELECT*FROMA CROSSJOINB
注意,在使用交叉连接操作符时,没有连接两个表的JOIN子句,就像在两个表之间执行内部和外部连接操作时一样。
您需要注意,使用交叉连接可以生成一个大型记录集。为了探索这种行为,让我们看两个不同的例子,说明交叉连接操作的结果集有多大。对于第一个示例,假设您交叉连接两个表,其中表A有10行,表B有3行,交叉连接的结果集将是10乘3或30行,第二个示例假设表A有1000万行,表B有300万行。在表A和表B之间的交叉连接结果集中将有多少行将是巨大的30.000.000000.000行。这是很多行,需要SQLServer大量的时间和大量资源来创建结果集,因此,在大型记录集中使用交叉联接运算符时,您需要小心。
通过几个例子让我们仔细看看如何使用交叉连接操作符,
使用交叉连接的基本实例
对于前几个例子,我们将加入两个示例表。清单1中的代码用于创建这两个示例表。确保在用户数据数据库中运行这些脚本,而不是在主数据库中运行这些脚本。
CREATETABLEProduct (ID int,
ProductName varchar(100),
Cost money);CREATETABLESalesItem (ID int,
SalesDate datetime,
ProductID int,
Qty int,
TotalSalesAmt money);INSERTINTOProduct
VALUES(1,'Widget',21.99),
(2,'Thingamajig',5.38),
(3,'Watchamacallit',1.96);INSERTINTOSalesItem
VALUES(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);
SELECT*FROM
Product CROSSJOINSalesItem;
对于第一个交叉联接示例,我将运行清单2中的代码
SELECT*FROM
Product CROSSJOINSalesItem;
清单2:交叉联接的例子
当我在SQLServerManagementStudio窗口中运行清单2中的代码时,使用会话设置以文本输出结果时,我在报表1中获得输出结果 .
ID ProductName Cost ID SalesDate ProductID Qty TotalSalesAmt
--- --------------------- -------- ---- ----------------------- --------- ---- ---------------
1 Widget 21.99 1 2014-10-01 00:00:00.000 1 1 21.99
1 Widget 21.99 2 2014-10-02 00:00:00.000 3 1 1.96
1 Widget 21.99 3 2014-10-03 00:00:00.000 3 10 19.60
1 Widget 21.99 4 2014-10-03 00:00:00.000 1 2 43.98
1 Widget 21.99 5 2014-10-03 00:00:00.000 1 2 43.98
2 Thingamajig 5.38 1 2014-10-01 00:00:00.000 1 1 21.99
2 Thingamajig 5.38 2 2014-10-02 00:00:00.000 3 1 1.96
2 Thingamajig 5.38 3 2014-10-03 00:00:00.000 3 10 19.60
2 Thingamajig 5.38 4 2014-10-03 00:00:00.000 1 2 43.98
2 Thingamajig 5.38 5 2014-10-03 00:00:00.000 1 2 43.98
3 Watchamacallit 1.96 1 2014-10-01 00:00:00.000 1 1 21.99
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
Report 1:运行清单2的结果
在运行清单2时如果您查看Report 1中的结果,您可以看到有15条不同的记录。前5条记录包含ProducttabJe的tbIe第一行的列值,这些列值与Salestein表中的5个不同行连接。对于Producttable的2秒和3行也是如此。返回的tota行数是Producttable中的行数乘以Saesltem表中的行数,即15行。
创建笛卡尔产品的一个原因可能是生成测试数据。假设我想使用ProductandSalesltem表中的D数据生成许多不同的产品,可以使用交叉连接来实现这一点,如清单3所示:
SELECTROW_NUMBER()OVER(ORDERBYProductName DESC)ASID,
Product.ProductName
+