高级 t - sql 第 1 级的阶梯 : 使用交叉连接来引入高级 t - sql
源于： 格雷戈里 ·拉森 ,2016/02/19( 首次出版 :2014/12/17
链接： http://www.sqlservercentral.com/articles/Stairway+Series/119933/
翻译：刘琼滨 谢雪妮 徐雅莉 赖慧芳
正文：
系列
本文是楼梯系列的一部分 : 高级 t - sql 的阶梯
这个楼梯将包含一系列的文章，这些文章将扩展到您在前面的两个 t - sql stairways 中学习的 t - sql 基础上，以及在基础之上的 t - sql DML 和 t - sql 的阶梯。这个楼梯应该帮助读者准备通过微软认证考试 70 - 461: 查询微软 SQL Server 2012 。
这是一个新的楼梯系列的第一篇文章，它将探索 Transact SQL(TSQL) 的更高级特性。这个楼梯将包含一系列的文章，这些文章将扩展到您在之前的两个 TSQL stairways 中学习的 TSQL 基础 :
··t - sql DML 楼梯
·· 楼梯 t - sql: 除了基础知识
这个 “高级 Transact SQL ”的楼梯将涵盖以下 TSQL 主题 :
··使用交叉连接操作符
··使用应用操作符
·· 了解公共表表达式 (CTE)
·· 创纪录水平处理使用 transact - sql 游标
··将使用主数据的支持
··使用透视将列进行
··订购您的数据使用排序的功能
··管理日期和时间函数
··了解在条款的变化
这个楼梯的读者应该已经很好地理解了如何从 SQL Server 表查询、更新、插入和删除数据。此外，他们应该有一个工作知识，这些方法可以用来控制他们的 TSQL 代码的流程，以及能够测试和操作数据。
这个楼梯应该帮助读者准备通过微软认证考试 70 - 461: 查询微软 SQL Server 2012 。
在这个新的楼梯系列的第一期中，我将讨论 CROSS JOIN 操作符。
CROSS JOIN 操作符介绍
交叉连接操作符可以将一个数据集的所有记录合并到另一个数据集中的所有记录。通过使用两组记录之间的交叉连接操作符，您创建了一个称为笛卡尔乘积的东西。
这里有一个简单的例子，使用 CROSS JOIN 操作符来连接两个表 A 和 B:
SELECT * FROM A CROSS JOIN B
注意，当使用交叉连接操作符时，没有连接子句连接两个表，就像在两个表之间执行内部和外部连接操作时使用的连接子句。
需要注意的是，使用交叉连接可以生成一个大的记录集。为了研究这种行为，我们来看看两个不同的例子，说明这个结果集的大小将来自于交叉连接操作。对于第一个示例，假设您是交叉连接两个表，其中表 A 有 10 行，表 B 有 3 行。一个交叉连接的结果集将是 10 乘以 3 或 30 行。对于第二个示例，假设表 A 有 1000 万行，表 B 有 300 万行。在表 a 和 B 之间的交叉连接结果中有多少行 ? 那将是一个巨大的 30000 亿行。这是大量的行，需要大量的时间和大量的资源来创建这个结果集。因此，在大型记录集上使用交叉连接操作符时需要非常小心。
让我们仔细研究一下使用 CROSS JOIN 操作符的一些例子。
使用交叉连接的基本示例
在前面的几个例子中，我们将会连接两个示例表。清单 1 中的代码将用于创建这两个示例表。确保在用户数据数据库中运行这些脚本，而不是在 master 中。
CREATE TABLE Product (ID int,
ProductName varchar(100),
Cost money);CREATE TABLE SalesItem (ID int,
SalesDate datetime,
ProductID int,
Qty int,
TotalSalesAmt money);INSERT INTO Product
VALUES (1,'Widget',21.99),
(2,'Thingamajig',5.38),
(3,'Watchamacallit',1.96);INSERT INTO SalesItem
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);
列表 1: 交叉连接的示例表
对于第一个交叉连接示例，我将运行 列表 2 中的代码。
SELECT * FROM
Product CROSS JOIN SalesItem;
列表 2: 简单的交叉连接示例
当我在一个 SQL Server Management Studio 窗口中运行 列表 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 2 43.98
报告 1: 运行 列表 2 的结果
如果你回顾报告 1 的结果，你可以看到有 15 个不同的记录。这些前 5 个记录包含从产品表的第一行与 SalesItem 表中 5 个不同的行连接的列值。同样适用于产品表的 2 秒和 3 行。返回的行数是 Product 表中的行数乘以 SalesItem 表中的行数，即 15 行。
创建 Cartesian 产品可能有用的一个原因是生成测试数据。假设我想在我的产品和 SalesItem 表中使用日期生成一些不同的产品。我可以使用一个交叉连接来实现，如 列表 3 所示 :
SELECT ROW_NUMBER() OVER(ORDER BY ProductName DESC) AS ID,
Product.ProductName
+ CAST(SalesItem.ID as varchar(2)) AS ProductName,
(Product.Cost / SalesItem.ID) * 100 AS CostFROM Product CROSS JOIN SalesItem;
列表 3: 简单的交叉连接示例
当我运行 列表 3 中的代码时，我得到了报告 2 中的输出。
ID ProductName Cost
----- ----------------------------------------------------------- ---------------------
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 列。此外，我使用 SalesItem 表中的 ID 列创建惟一的 ProductName 和成本列值。产生的行数等于产品表中的行数乘以 SalesItem 表中的行数。
到目前为止，本节中的示例只执行了跨两个表的交叉连接。可以使用 CROSS JOIN 操作符跨多个表执行交叉连接操作。 列表 4 中的示例在三个表中创建了一个 Cartesian 产品。
SELECT * FROM sys.tables CROSS JOIN sys.objectsCROSS JOIN sys.sysusers;
列表 4: 使用 CROSS JOIN 操作符创建三个表的 Cartesian 产品
运 行列表 4 的输出有两个不同的 CROSS_JOIN 操作。由该代码创建的 Cartesian 产品将产生一个结果集，其总行数等于 sys 中的行数。表乘以 sys 中的行数。对象乘以 sysusers 中的行数。
当交叉连接执行类似于内部连接时
在前面的部分中，我提到过，当使用交叉连接运算符时，它会产生一个笛卡尔积。这不是真的。当您使用 WHERE 子句约束连接到跨连接操作 SQL Server 的表时，不会创建笛卡尔产品。相反，它的功能类似于普通的连接操作。为了演示这种行为，请查看 列表 5 中的代码。
SELECT * FROM Product P CROSS JOIN SalesItem SWHERE P.ID = S.ProductID;
SELECT * FROM Product P INNER JOIN SalesItem SON P.ID = S.ProductID;
列表 5: 两个等价的 SELECT 语句。
列表 5 中的代码包含两个 SELECT 语句。第一个 SELECT 语句使用 CROSS JOIN 操作符，然后使用 WHERE 子句定义如何连接到交叉连接操作中的两个表。第二个 SELECT 语句使用一个正常的内部连接操作符，并使用一个 ON 子句来连接这两个表。 SQL Server 的查询优化器足够聪明，可以知道 列表 5 中的第一个 SELECT 语句可以作为内部连接重新编写。优化器知道，当使用交叉连接操作时，它可以重新编写查询，与在交叉连接中涉及的两个表之间提供连接谓词的 WHERE 子句一起使用。因此， SQL Server 引擎为 列表 5 中的 SELECT 语句生成相同的执行计划。当您不提供一个约束 SQL 服务器不知道如何连接跨连接操作的两个表时，它会在与交叉连接操作相关联的两个集合之间创建一个 Cartesian 产品。
使用交叉连接查找未销售的产品
在前面的小节中找到的示例是为了帮助您理解 CROSS JOIN 操作符以及如何使用它。使用 CROSS JOIN 操作符的一个功能是使用它来帮助在一个表中查找与另一个表中没有匹配记录的项。例如，假设我想要在我的产品表中每一个产品被售出的每一个日期，报告我的产品表中每个产品名称的总数量和总销售额。因为在我的例子中，每一个产品的名字都不是每天都有销售，我的报告要求是我需要显示一个 0 的数量和总的销售额的 0 美元，因为这些产品在某一天没有销售。这是交叉连接操作符与左外 JOIN 操作的结合，它将帮助我识别那些在给定的一天中没有被出售的项目。满足这些报告需求的代码如 列表 6 所示 :
SELECT S1.SalesDate, ProductName
, ISNULL(Sum(S2.Qty),0) AS TotalQty
, ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSalesFROM Product PCROSS JOIN (SELECT DISTINCT SalesDate FROM SalesItem
) S1LEFT OUTER JOIN
SalesItem S2ON P.ID = S2.ProductIDAND S1.SalesDate = S2.SalesDateGROUP BY S1.SalesDate, P.ProductNameORDER BY S1.SalesDate;
列表 6: 查找不使用交叉连接销售的产品
让我带你走过这段代码。我创建了一个子查询，它选择所有不同的 SalesDate 值。这个子查询提供了所有的日期，其中有一个销售。然后我将它与我的产品表连接起来。这允许我在每个销售日期和每个产品行之间创建一个 Cartesian 产品。从交叉连接返回的集合将具有在最终结果集中所需要的所有值，除了每个产品的 Qty 和 TotalSalesAmt 的总和。为了获得这些汇总值，我在 SalesItem 表上执行一个左外连接，并与通过 CROSS JOIN 操作创建的 Cartesian 产品连接。我基于 ProductID 和 SalesDate 列执行了此连接。通过使用我的 Cartesian 产品中的左外联接来返回，如果有一个与 ProductID 和 SalesDate 相匹配的 SalesDate 记录，那么 Qty 和 TotalSalesAmt 值将与相应的行相关联。这个查询的最后一件事是使用 GROUP BY 子句来总结基于 SalesDate 和 ProductName 的 Qty 和 TotalSalesAmount 。
性能考虑
产生笛卡尔积的交叉连接运算符有一些性能方面需要考虑。因为 SQL 引擎需要在一个集合中加入每一行，而在另一个集合中，结果集可以相当大。如果我做一个交叉连接一个表有 1,000,000 行和另一个表有 100,000 行那么我的结果集就会有 1,000,000 X 10 万行，或者说 100,000,000,000 行。这是一个很大的结果集，它将花很多时间来创建它。
交叉连接操作符可以是一个很好的解决方案，可以在所有可能的组合中确定一个结果集，就像所有客户的每个月的所有销售，即使在几个月的时间里，一些客户没有销售。在使用 CROSS JOIN 操作符时，如果希望优化性能，应该尽量减少交叉联接的大小。例如，假设我有一个表，其中包含过去两个月的销售数据。如果我想要生成一个报告，显示一个月没有销售的客户，那么确定一个月的天数的方法可以极大地改变我的查询的性能。为了证明这一点，我首先为 1000 名客户创造了一个为期两个月的销售记录。我将使用 列表 7 中的代码来实现这一点。
CREATE TABLE Cust (Id int, CustName varchar(20));CREATE TABLE Sales (Id int identity
,CustID int
,SaleDate date
,SalesAmt money);SET NOCOUNT ON;DECLARE @I int = 0;DECLARE @Date date;WHILE @I < 1000BEGIN
SET @I = @I + 1;
SET @Date = DATEADD(mm, -2, '2014-11-01');
INSERT INTO Cust
VALUES (@I,
'Customer #' + right(cast(@I+100000 as varchar(6)),5));
WHILE @Date < '2014-11-01'
BEGIN
IF @I%7 > 0
INSERT INTO Sales (CustID, SaleDate, SalesAmt)
VALUES (@I, @Date, 10.00);
SET @Date = DATEADD(DD, 1, @Date);
ENDEND
列表 7:TSQL 为性能测试创建示例数据
列表 7 中的代码为 1000 个不同的客户创建了两个月的数据。这段代码没有为每 7 个客户增加销售数据。这段代码产生了 1000 个 Cust 表记录和 52,338 个销售表记录。
为了演示如何使用交叉连接操作符执行不同的操作，这取决于跨连接输入集中使用的集合的大小，让我来运行 列表 8 和 列表 9 中的代码。对于每个测试，我将记录返回结果所需的时间。
SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName,
ISNULL(SUM(S2.SalesAmt),0) AS TotalSalesFROM Cust CCROSS JOIN (SELECT SaleDate FROM Sales ) AS S1LEFT OUTER JOIN
Sales S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDateGROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0) = 0ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName
列表 8: 与所有销售记录交叉连接
SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName,
ISNULL(SUM(S2.SalesAmt),0) AS TotalSalesFROM Cust CCROSS JOIN (SELECT DISTINCT SaleDate FROM Sales ) AS S1LEFT OUTER JOIN
Sales S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDateGROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0) = 0ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName
列表 9: 与不同的销售日期列表交叉连接
在 列表 8 中， CROSS JOIN 操作符加入了 1000 个 Cust 记录，其中有 52,338 个销售记录，生成一个创纪录的 52338000 行的记录集，然后用来确定一个月销售为零的客户。在 列表 9 中，我将选择标准从 Sales 表中更改为只返回一组不同的 SalesDate 值。这个独特的集合只产生了 61 个不同的销售日期值，因此 列表 9 中的 CROSS JOIN 操作的结果只产生了 61,000 条记录。通过减少交叉连接操作的结果集，清单 9 中的查询运行不到 1 秒，而 列表 8 中的代码在我的机器上运行了 19 秒。这种性能差异的主要原因是记录 SQL Server 需要处理每个查询执行的不同操作的数量。如果您查看两个清单的执行计划，您将看到计划略有不同。但是，如果您看一下嵌套循环 (Inner Join) 操作所生成的记录的数量，在图形化计划的右侧，您将看到 列表 8 估计有 52338000 条记录，而 列表 9 中的操作仅估计有 61,000 条记录。这个巨大的记录集， 列表 8 的查询计划从交叉连接嵌套循环操作中生成，然后再传递到几个额外的操作。因为 列表 8 中的所有操作都必须处理 5200 万的记录。 列表 8 比 列表 9 慢得多。
正如您所看到的，交叉连接操作中使用的记录数可以极大地影响查询运行的时间长度。因此，如果您可以编写您的查询来最小化交叉连接操作中涉及的记录的数量，那么您的查询将执行得更有效率。
结论
交叉连接运算符在两个记录集之间产生一个笛卡尔积。这个操作符有助于识别一个表中没有与另一个表中匹配的记录的项。应注意尽量减少与交叉连接操作符使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码尽可能快地运行。
注：本文内容来自互联网，旨在为开发者提供分享、交流的平台。如有涉及文章版权等事宜，请你联系站长进行处理。