阶梯到高级T-SQL 1级：高级T-SQL介绍交叉连接

阶梯到高级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

+cast(salesitem.id asvarchar(2))asproductname,

(product.cost /salesitem.id)*100 ascostfromproduct crossjoinsalesitem;

清单3：简单的交叉连接例子

当我运行清单3中的代码时，获得report 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

report2：；运行清单3的结果

正如您可以看到的，通过查看清单3中的代码，我生成了许多行，其中包含类似于我的producttable中的数据的数据。通过使用row_number函数，我能够在每一行上生成唯一的id列。此外，我还使用salesltem表中的id列来创建唯一的productname和成本列值。生成的行数等于producttable中的行数乘以salesltem表中的行数。

到目前为止，本节中的示例只对两个表执行了交叉连接。您可以使用交叉连接操作符来跨多个表执行交叉连接操作，清单4中的例子将在三个表中创建一个笛卡儿管道。

select*fromsys.tables crossjoinsys.objectscrossjoinsys.sysusers;

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

运行清单4的输出有两个不同的交叉_连接操作。从这段代码创建的笛卡尔产品将产生一个结果集，其总行数等于sys.table中行数的j乘以sys.objects中的行数。

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

在前面的部分，我提到当你使用交叉连接算子时，它会产生笛卡儿乘积。这不是一直都是这样的。当您使用限制交叉连接操作中涉及的表的联接的where子句时，sqlserver不会创建笛卡尔产品。相反，它的功能类似于普通的联接操作。要演示这种行为，请查看代码在清单5。

select*fromproduct p crossjoinsalesitem swherep.id =s.productid;

select*fromproduct p innerjoinsalesitem sonp.id =s.productid;

清单5：两个等价的select语句

清单5中的代码包含两个select语句。第一个select语句使用交叉连接操作符，然后使用where子句定义如何连接在交叉连接操作中开具发票的两个表。第二个select语句使用一个普通的带有on子句的内部jojn操作符来连接这两个表。sqlserver的查询优化器非常聪明，可以知道清单5中的第一个select语句可以重写为内部联接。当交叉连接操作与where子句一起使用时，优化器知道可以重写查询。where子句在交叉连接所涉及的两个表之间提供连接谓词。因此，sqlsener引擎为清单5中的两个select语句生成相同的执行计划。当您不提供where约束时，sqlserver不知道如何加入涉及交叉连接操作的两个表，因此它在与交叉连接关联的两个集合之间创建一个笛卡儿积。

 

用交叉连接找出未销售产品

在前面章节中的例子有助于你理解cross join 操作并且理解如何使用它。cross join运算符的一个功能是用它来在另一个表中帮助查找一个表中没有匹配记录的项。例如，假设在我的产品表中列出了我的任何一个产品的销售日期我想要报告总数量和总销售额对每个产品名称。因为在我的例子中产品名称不是每天都有销售，我的报告要求意味着我需要展示在给定日期没销售的产品的数量0和销售总额0美元。交叉联接运算符与左外部联接操作一起在这将帮助我识别那些在给定日期内尚未销售的产品。6中可以找到满足这些报告要求的代码:

selects1.salesdate,productname

,isnull(sum(s2.qty),0)astotalqty

,isnull(sum(s2.totalsalesamt),0)astotalsales

fromproduct p

crossjoin

(

selectdistinctsalesdate fromsalesitem

)s1

leftouterjoin

salesitem s2

onp.id =s2.productid

ands1.salesdate =s2.salesdate

group bys1.salesdate,p.productname

orderbys1.salesdate;

让我通过这个代码向你介绍。我创建一个子查询来选择所有不同的销售日期值。此子查询提供了销售的所有日期。然后交叉加入我的产品表。这允许我创建一个cartesian在销售日期每一个结果行之间。从交叉联接返回的集合有我需要的所有值将在最终结果集中除了qty的和和销售总额每售出一件产品。为了获得这些值我用笛卡尔积操作和一个左侧外连接来得到销售项目表。我是根据产品编号和销售日期列。通过使用左外部联接，将返回我的笛卡尔乘积中的每一行，如果匹配销售日期的记录产品编号和销售日期，问总产量和销售总额值将与适当的行相链接。这个查询的最后一件事是使用group by子句来总结数量和销售总额基于销售日期和产品名称。

性能考虑

生成笛卡尔乘积的交叉连接运算符需要考虑一些性能方面。因为sql引擎需要将加入一个集合的每一行与另一个集结果集可能会很大。如果将一个具有1000000行的表与另一个具有100000行的表交叉连接，则我的结果集将具有1000000 x 100000行，即100000000行。这是一个很大的结果集，需要花费sql server大量的时间去创建。

cross join操作可以是一个很好的解决方案，对于识别两个集合的所有可能组合中的一个结果集，像每个月所有客户的所有销售一样，即使有些客户有几个月没有销售。当使用交叉连接运算符时，你应尽量减小交叉连接的集的大小如果要优化性能。例如，假设我有一个表，其中包含过去2个月的销售数据。如果我想生成一个显示一个月内没有任何销售的客户的报告，那么确定一个月内天数的方法可以极大地改变我的查询。为了证明这一点，首先让我为1000名客户创建一组为期两个月的销售记录。我将使用7中的代码执行此操作。

createtablecust (id int,custname varchar(20));

createtablesales (id intidentity

,custid int

,saledate date

,salesamt money);

setnocount on;

declare@i int=0;

declare@date date;

while@i

begin

set@i =@i +1;

set@date =dateadd(mm,-2,'2014-11-01');

insertintocust

values(@i,

'customer #'+right(cast(@i+100000 asvarchar(6)),5));

while@date

begin

if@i%7 >0

insertintosales (custid,saledate,salesamt)

values(@i,@date,10.00);

set@date =dateadd(dd,1,@date);

end

end

7中的代码为1000个不同的客户创建了价值2个月的数据。代码添加每7个客户的不销售数据。此代码生成1000客户表记录和52 338销售表记录。

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

selectconvert(char(6),s1.saledate,112)assalesmonth,c.custname,

isnull(sum(s2.salesamt),0)astotalsales

fromcust c

crossjoin

(

selectsaledate fromsales

)ass1

leftouterjoin

sales s2

onc.id =s2.custid

ands1.saledate =s2.saledate

group byconvert(char(6),s1.saledate,112),c.custname

havingisnull(sum(s2.salesamt),0)=0

orderbyconvert(char(6),s1.saledate,112),c.custname

selectconvert(char(6),s1.saledate,112)assalesmonth,c.custname,

isnull(sum(s2.salesamt),0)astotalsales

fromcust c

crossjoin

(

selectdistinctsaledate fromsales

)ass1

leftouterjoin

sales s2

onc.id =s2.custid

ands1.saledate =s2.saledate

group byconvert(char(6),s1.saledate,112),c.custname

havingisnull(sum(s2.salesamt),0)=0

orderbyconvert(char(6),s1.saledate,112),c.custname

在8中，交叉连接运算符连接了1000个客户有52 338项记录来生成一组52338000行的记录集，然后用于确定一个月内销售为零的客户。在9中，我从销售表只返回一组不同的销售日期价值观。这个不同的集合只产生61个不同的销售日期值，因此清单9中交叉连接操作的结果只生成61000条记录。通过减少交叉连接操作的结果集， 9中的查询的运行时间不到1秒，而8中的代码在我的计算机上运行19秒。造成这种时间差异的主要原因是sql server需要为每个查询执行的不同操作处理大量记录。如果你查看两个列表的执行计划，将你会发现计划略有不同。但是，如果你从嵌套循环操作生成的大概记录数看，在图形计划的右侧，你将会看到8大约有52338000条记录，然而9中的相同操作只有大约61000条记录。8查询计划从交叉连接嵌套循环操作被传递到几个附加操作在这个巨大记录集中。因为8中的所有这些操作都针对5200万条记录。所以8比9慢很多。

正如你所见，交叉连接操作中使用的记录数量会明显影响查询运行的时长。因此如果你可以以最小化交叉连接操作中涉及的记录数来编写你的查询，则你的查询的执行效率将大大提高。

结论

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

问题和回答

在本节中，你可以通过回答以下问题来回顾你对交叉连接运算符的理解。

问题1

交叉联接运算符通过匹配两个记录集在on子句指定的列的基础上来创建结果集。(对的还是错)？

对的

错的

问题2

哪个公式可以用来区分从两个表a和表b之间的无约束交叉联接返回的行数，当表a和表b包含重复行时？

表a中的行数乘以表b中的行数

表a中的行数乘以表b中的唯一行的数

表a中的唯一行的数乘以表b中的行数

表a中的唯一行的数乘以表b中的唯一行的数

问题3

哪种方法提供了减小交叉连接操作产生的笛卡尔乘积大小的最佳机会？

确保连接的两组的行尽可能多

确保连接的两组的行尽可能少

确保交叉连接操作左边的集的行尽可能少

确保交叉连接操作右边的集的行尽可能少

回答

问题1

正确答案是b。交叉连接运算符不使用on子句执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。在连接两个集合时交叉连接创建笛卡尔乘积。

问题2

正确答案是a、b、c，d不正确，因为当为交叉连接操作创建笛卡尔积时表a或b中重复行也参加。

问题3

正确的答案是b。通过减小交叉连接操作中涉及的两个集合的大小，可以最小化由交叉链接操作创建的最终集合的大小。c和d还有助于减小由交叉连接操作创建的最后集的大小，但可能不确保交叉连接操作中涉及的两个集具有最少的行。

如对本文有疑问， 点击进行留言回复！！