高级T-SQL交叉连接-CSDN博客

高级T-SQL SQL 1级：使用交叉连接介绍高级T-SQL

该系列：

本文是楼梯系列的一部分：高级T-SQL的楼梯

这个楼梯将包含一系列文章，将扩展到T-SQL基金会，你在前两个T-SQL楼梯，楼梯到T-SQL DML和T-SQL之外的基础知识。这个楼梯应该帮助读者准备通过微软认证考试74-461：查询微软SQL Server 2012。

这是一个新的楼梯系列文章中的第一篇文章，它将探讨Transact-SQL（TSQL）的更高级的特性。这个楼梯将包含一系列的文章，这些文章将在前面两个TSQL楼梯上学到的TSQL基础上扩展：

到T-SQL DML的楼梯

通往T-SQL的楼梯：超越基础

这个“高级Transact-SQL”楼梯将涵盖以下TSQL主题：

使用交叉连接算子

使用Apple算子

理解常用表表达式（CTE）

使用Transact-SQL光标进行记录级处理

利用枢轴旋转数据

使用UNPIVOT将列转换为行

使用排序函数排序数据

用函数管理日期和时间

理解过句的变化

这个楼梯的读者应该已经很好地理解了如何查询、更新、插入和删除SQL Server表中的数据。此外，他们应该有一个工作方法的知识，可以用来控制他们的TSQL代码的流动，以及能够测试和操作数据。

这个楼梯应该帮助读者准备通过微软认证考试74-461：查询微软SQLServer 2012。

对于新的楼梯系列的第一部分，我将讨论交叉连接运算符。

交叉连接算子介绍

交叉连接运算符可以用来将一个数据集中的所有记录合并到另一个数据集中的所有记录。通过使用两组记录之间的交叉连接操作符，你创造了被称为笛卡尔积的产品。

下面是使用交叉连接运算符来连接两个表A和B的简单示例：

 SELECT*FROM A CROSSJOIN B

注意当使用交叉连接运算符时，没有连接子句来连接两个表，就像在执行两个表之间的内部和外部连接操作时所使用的连接表一样。您需要知道，使用交叉连接可以产生一个大的记录集。为了探究这种行为，让我们来看两个不同的例子，即从交叉连接操作中得到的集合有多大。对于第一个例子，假设您是交叉连接两个表，其中表A有10行，表B有3行。交叉连接的结果集将是10次3或30行。对于第二个例子，假定表A有1000万行，表B有300万行。表A和B之间的交叉连接结果集将有多少行？那将是30000000000000排。这是很多行，它将花费大量的时间和大量的资源来创建这个结果集。因此，在大型记录集上使用交叉连接运算符时需要小心。

让我们仔细研究一下使用交叉连接运算符的例子。

使用交叉连接的基本示例

对于第一个例子，我们将加入两个样本表。清单1中的代码将用于创建这两个示例表。请确保在用户数据数据库中运行这些脚本而不在主服务器中运行。

CREATETABLE Product (ID int,

                      ProductName varchar(100),

                      Cost money);CREATETABLE SalesItem (ID int,

                        SalesDate datetime,

                        ProductID int,

                        Qty int,

                        TotalSalesAmt money);INSERTINTO Product

        VALUES(1,'Widget',21.99),

               (2,'Thingamajig',5.38),

                  (3,'Watchamacallit',1.96);INSERTINTO 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 CROSSJOIN 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个记录包含来自产品表的第一行的列值，与SaleTI目表中的5个不同行连接。对于产品表的2秒和3行也是如此。返回的行总数是Product表中的行数乘以SalestItTABLE表中的行数，这是15行。

创建笛卡尔产品的一个原因可能是有用的，就是生成测试数据。假设我想使用我的产品和SaltItIt表中的日期生成许多不同的产品。我可以使用交叉连接，如清单3所做的那样：

SELECTROW_NUMBER()OVER(ORDERBY ProductName DESC)AS ID,

       Product.ProductName

           + CAST(SalesItem.ID asvarchar(2))AS ProductName,

       (Product.Cost / SalesItem.ID)* 100 AS CostFROM Product CROSSJOIN 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中的代码，我生成了许多行，这些行包含与我的产品表中的数据类似的数据。通过使用RooLoNoT函数，我能够在每一行上生成唯一的ID列。此外，我使用了SaltItIt表中的ID列来创建唯一的产品名称和成本列值。生成的行数等于乘积表中的行数乘以SaltItIt表中的行数。

本节中的示例仅在两个表之间执行交叉连接。可以使用交叉连接运算符跨多个表执行交叉连接操作。清单4中的示例在三个表中创建了笛卡尔积。

SELECT*FROM sys.tables CROSSJOIN sys.objectsCROSSJOIN sys.sysusers;

清单4：使用交叉连接运算符创建三个表的笛卡尔积

运行清单4的输出有两个不同的交叉连接操作。由该代码创建的笛卡尔积将产生一个结果集，该集合将具有与Syts表中的行数相等的总行计数，同时，SysObjts中的行数乘以SysSysAsple中的行数。

当交叉连接像内部连接一样执行时

在前一节中，我提到当使用交叉连接运算符时，它会产生笛卡尔积。这不是一直都是真的。当使用WHERE子句限制交叉连接操作中涉及的表的连接时，SQLServer不创建笛卡尔积。相反，它的功能类似于正常的连接操作。为了演示这种行为，请查看清单5中的代码。

SELECT*FROM Product P CROSSJOIN SalesItem SWHERE P.ID = S.ProductID;

SELECT*FROM Product P INNERJOIN SalesItem SON P.ID = S.ProductID;

清单5：等价的两个SELECT语句。

清单5中的代码包含两个SELECT语句。第一个SELECT语句使用交叉连接运算符，然后使用WHERE子句定义如何加入交叉连接操作中涉及的两个表。第二个SELECT语句使用一个带有ON子句的正常内部联接运算符来加入这两个表。SQL Server的查询优化器足够聪明，可以知道清单5中的第一个SELECT语句可以被重新写入内部连接。优化器知道，当交叉连接操作与WHERE子句一起使用时，可以重新编写查询，该WHERE子句在交叉连接中涉及的两个表之间提供连接谓词。因此，SQLServer引擎为清单5中的两个SELECT语句生成相同的执行计划。当不提供一个WHERE约束SQL Server不知道如何加入涉及交叉连接操作的两个表时，它在与交叉连接操作相关联的两个集合之间创建一个笛卡尔积。

使用交叉连接查找未售出的产品

前面几节中的例子帮助您理解交叉连接运算符以及如何使用它。使用交叉连接运算符的一个功能是使用它来帮助查找在另一个表中没有匹配记录的一个表中的项。例如，假设我想报告我的产品表中的每一个产品名称的总数量和总销售金额。因为在我的例子中，每一个产品名称都不是每天都有销售，所以我的报告要求意味着我需要显示一个数量0和总销售额0美元的产品，这些产品在某一天没有销售。这是交叉连接操作符与左外部连接操作一起帮助我识别那些在某一天没有销售的项目。满足这些报表要求的代码可以在清单6中找到：

SELECT S1.SalesDate, ProductName

     , ISNULL(Sum(S2.Qty),0)AS TotalQty

         , ISNULL(SUM(S2.TotalSalesAmt),0)AS TotalSalesFROM Product PCROSSJOIN  (SELECTDISTINCT SalesDate FROM SalesItem

  ) S1LEFTOUTERJOIN

SalesItem S2ON P.ID = S2.ProductIDAND S1.SalesDate = S2.SalesDate

GROUP BY S1.SalesDate, P.ProductNameORDERBY S1.SalesDate;

清单6：查找不使用交叉连接销售的产品

让我给你介绍一下这个密码。我创建一个子查询来选择所有不同的销售日期值。这个子查询给了我所有的销售日期。然后我与我的产品表交叉连接。这允许我在每个销售日期和每个产品行之间创建一个笛卡尔积。从交叉连接返回的集合将具有最终结果集中所需的每一个值，除了销售的每个产品的QTy和ToeSaleSAMT的总和。为了获得这些汇总值，我对SaleItIt表执行左外部连接，将其与与交叉连接操作创建的笛卡尔积一起加入。我根据StudioD和SaleDead列执行了这个连接。通过使用左外部连接，我的笛卡尔积中的每一行将被返回，并且如果有一个匹配的销售日期记录用于产品和销售日期，QTY和TooStutsAtMt值将与适当的行相关联。这个查询的最后一件事是使用GROUPBY子句总结基于销售日期和产品名称的QTY和ToalSaleStalk。

绩效考量

产生笛卡尔积的交叉连接算子有一些性能方面需要考虑。因为SQL引擎需要在一个集合中的每一行中加入另一个集合中的每一行，所以结果集可以相当大。如果我做一个交叉连接一个表，它有1000000行，另一个表有100000行，那么我的结果集将有1000000×100000行，或者100000000000行。这是一个大的结果集，它将花费大量的时间来创建SQL Server。

交叉连接操作符可以是一个很好的解决方案，用于识别两个集合中所有可能组合的结果集，比如每个月的所有客户的所有销售，甚至在几个月内一些客户没有销售。当使用交叉连接运算符时，如果要优化性能，则应该尽量减少交叉连接的集合的大小。例如，假设我有一个表，包含过去2个月的销售数据。如果我想制作一个报告，显示一个月没有销售的客户，那么一个月内的天数的识别方法会极大地改变我的查询的性能。为了演示这一点，首先让我为1000个客户创建一组为期两个月的销售记录。我将使用清单7中的代码来实现这一点。

CREATETABLE Cust (Id int, CustName varchar(20));CREATETABLE Sales (Id intidentity

                    ,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');

        INSERTINTO Cust

        VALUES(@I,

                'Customer #'+right(cast(@I+100000 asvarchar(6)),5));

        WHILE@Date <'2014-11-01'

        BEGIN

               IF@I%7 > 0

                       INSERTINTO Sales (CustID, SaleDate, SalesAmt)

                       VALUES(@I,@Date, 10.00);

               SET@Date =DATEADD(DD, 1,@Date);

        ENDEND

清单7：TSQL创建用于性能测试的示例数据

清单7中的代码为1000个不同的客户创建了2个月的数据值。此代码不为每第七个客户添加销售数据。此代码产生1000个CUSTT表记录和52338个销售表记录。

为了演示如何使用交叉连接运算符根据交叉连接输入集中使用的集合的大小来执行不同的操作，让我运行清单8和清单9中的代码。对于每个测试，我将记录返回结果所需的时间。

SELECTCONVERT(CHAR(6),S1.SaleDate,112)AS SalesMonth, C.CustName,

       ISNULL(SUM(S2.SalesAmt),0)AS TotalSalesFROM Cust CCROSSJOIN  (SELECT SaleDate FROM Sales )AS S1LEFTOUTERJOIN

Sales  S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDate

GROUP BYCONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0)= 0ORDERBYCONVERT(CHAR(6),S1.SaleDate,112),C.CustName

清单8：与所有销售记录交叉连接

SELECTCONVERT(CHAR(6),S1.SaleDate,112)AS SalesMonth, C.CustName,

       ISNULL(SUM(S2.SalesAmt),0)AS TotalSalesFROM Cust CCROSSJOIN  (SELECTDISTINCT SaleDate FROM Sales )AS S1LEFTOUTERJOIN

Sales  S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDate

GROUP BYCONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0)= 0ORDERBYCONVERT(CHAR(6),S1.SaleDate,112),C.CustName

清单9：针对不同的销售日期列表交叉连接

在清单8中，交叉连接运算符将1000个CUSTS记录与52338个销售记录相结合，以产生52338000行的记录集，然后用于确定在一个月内零销售的客户。在清单9中，我从销售表中更改了我的选择标准，只返回了一组不同的销售日期值。这个不同的集合只产生61个不同的SaleDead值，所以清单9中的交叉连接操作的结果只产生61000条记录。通过减少交叉连接操作的结果集，清单9中的查询在1秒内运行，而清单8中的代码在我的机器上运行19秒。这种性能差异的主要原因是SQLServer需要处理的记录卷对于每个查询执行的不同操作。如果查看两个清单的执行计划，您会发现计划略有不同。但是，如果您查看从嵌套循环（内部联接）操作生成的记录的估计数，在图形计划的右侧，您将看到清单8估计52338000个记录，而清单9中的相同操作仅估计61000个记录。从交叉连接嵌套循环操作生成清单8查询计划的这个大记录集然后传递给多个附加操作。因为清单8中的所有这些操作必须对5200万条记录进行操作。清单8比清单9要慢得多。

正如您所看到的，交叉连接操作中使用的记录数量会极大地影响查询运行的时间长度。因此，如果可以编写查询以最小化交叉连接操作中涉及的记录的数量，则查询将更有效地执行。

结论

交叉连接算子在两个记录集之间产生笛卡尔积。此运算符有助于识别一个表中没有另一个表中匹配记录的项。应该注意最小化交叉连接操作符使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码尽可能快地运行。

问答

在本节中，您可以通过回答以下问题来查看使用交叉连接运算符的理解程度。

问题1：

交叉连接运算符通过根据ON子句中指定的列匹配两个记录集来创建结果集。（真的还是假的）？

真的

错误的

问题2：

当表A和B包含重复行时，哪一个公式可以用来标识从两个表A和B之间的无约束交叉连接返回的行数？

表A中的行数乘以表B中的行数