菜单

翻:高级t – sql第1层的阶梯:使用交叉连接来引入高档t – sqlt-sql

2018年9月21日 - betway体育

高等t – sql第1级的台阶:使用交叉连接来引入高档t – sql

尖端t – sql第1层的阶梯:使用交叉连接来引入高档t – sql

自:格雷戈里·拉森,2016/02/19(首差出版:2014/12/17

出自:格雷戈里·拉森,2016/02/19(首浅出版:2014/12/17

翻译:刘琼滨 谢雪妮 徐雅莉 赖慧芳

链接:http://www.sqlservercentral.com/articles/Stairway+Series/119933/

链接:http://www.sqlservercentral.com/articles/Stairway+Series/119933/

 

 

翻译:刘琼滨 谢雪妮 徐雅莉 赖慧芳

正文:

正文:

系列

系列

本文是阶梯系列之均等片段:高级t – sql的台阶

正文是楼梯系列的同一有些:高级t – sql的阶梯

本条楼梯将包含一多级的章,这些章用扩充至公当眼前的星星个t

是楼梯将含有一名目繁多的篇章,这些章用扩充及您当前方的少只t

随即是一个新的阶梯系列之率先篇文章,它将探索Transact
SQL(TSQL)的更高级特性。这个楼梯将涵盖一雨后春笋的文章,这些文章用扩充及您当前的鲜独TSQL stairways中读之TSQL基础:

立刻是一个初的阶梯系列的第一首稿子,它以探索Transact
SQL(TSQL)的复尖端特性。这个楼梯将包含一文山会海之章,这些章用扩展至公当之前的鲜个TSQL stairways中上的TSQL基础:

··t – sql DML楼梯

··t – sql DML楼梯

··楼梯t – sql:除了基础知识

··楼梯t – sql:除了基础知识

这“高级Transact SQL”的梯子将含有以下TSQL主题:

是“高级Transact SQL”的梯子将包含以下TSQL主题:

··使用交叉连接操作符

··使用交叉连接操作符

··使用使用操作符

··使用应用操作符

··了解公共表表达式(CTE)

··了解公共表表达式(CTE)

··创纪录水平处理下transact –
sql游标

··创纪录水平处理利用transact –
sql游标

··将使用主数据的支持

··将使用主数据的支撑

··使用透视将列进行

··使用透视将列进行

··订购您的数采取排序的作用

··订购您的数码利用排序的职能

··管理日期与日函数

··管理日期及时函数

··了解在条款的变迁

··了解在条款的扭转

以此楼梯之读者应当已经颇好地了解了安自SQL
Server表查询、更新、插入和去数据。此外,他们理应生出一个工作知识,这些主意可就此来支配他们的TSQL代码的流程,以及能测试和操作数据。

此楼梯的读者应当都不行好地领略了怎样由SQL
Server表查询、更新、插入和去数据。此外,他们相应产生一个办事知识,这些点子可以用来决定他们之TSQL代码的流水线,以及能测试与操作数据。

是楼梯应该拉读者准备通过微软认证考试70

以此楼梯应该拉读者准备通过微软认证考试70

于这新的阶梯系列的第一想吃,我将讨论CROSS
JOIN操作符。

于这新的梯子系列的首先希望被,我将讨论CROSS
JOIN操作符。

CROSS JOIN操作符介绍

CROSS JOIN操作符介绍

交叉连接操作符可以用一个数据集的兼具记录合并到其他一个数汇总之拥有记录。通过以有限组记录里的接力连接操作符,您创建了一个叫作笛卡尔乘积的东西。

接力连接操作符可以将一个数据集的有所记录合并及其它一个数额集中的装有记录。通过以有限组记录中的穿插连接操作符,您创建了一个叫做笛卡尔乘积的物。

此间有一个简的例子,使用CROSS
JOIN操作符来连续两单表A和B:

这边产生一个简短的例证,使用CROSS
JOIN操作符来连接两单表A和B:

 SELECT * FROM A CROSS JOIN B

 SELECT * FROM A CROSS JOIN B

留意,当用交叉连接操作符时,没有连接子句子连接两独说明,就如以两个表之间实行中以及表面连接操作时使用的连接子句。

注意,当用交叉连接操作符时,没有连接子句子连接两独说明,就如以两个表之间实行中与外部连接操作时行使的连接子句。

急需留意的凡,使用交叉连接可以很成一个十分之记录集。为了研究这种表现,我们来瞧两个不同的例子,说明这结果集的大小将来自于交叉连接操作。对于第一个示范,假要您是交叉连接两独说明,其中表A有10实行,表B有3行。一个接力连接的结果集将是10乘机以3要30实施。对于第二只示范,假设表A有1000万履,表B有300万执。在表a和B之间的交叉连接结果中有些许行?那用是一个高大的30000亿履行。这是大方底实施,需要大量之时刻跟大气底资源来创造是结果集。因此,在巨型记录集及动交叉连接操作符时需要大小心。

待注意的凡,使用交叉连接可以很成一个良的记录集。为了研究这种表现,我们来探望两只不等之例子,说明是结果集的大小将来自于交叉连接操作。对于第一只示范,假要您是穿插连接两单说明,其中表A有10实践,表B有3行。一个接力连接的结果集将凡10趁以3要么30行。对于第二独示范,假设表A有1000万推行,表B有300万履行。在表a和B之间的接力连接结果中来小行?那将凡一个光辉的30000亿执。这是大方之尽,需要大量底时空以及大度的资源来创造是结果集。因此,在巨型记录集上运用交叉连接操作符时需要特别小心。

被咱密切研究一下用CROSS
JOIN操作符的部分例。

吃咱密切研究一下下CROSS
JOIN操作符的片例。

运用交叉连接的主导示例

运交叉连接的中坚示例

以前头的几乎只例子中,我们将会晤一连两单示例表。清单1中的代码用用来创造这有限只示例表。确保于用户数量数据库中运作这些本子,而无是当master中。

以面前的几乎单例中,我们拿会晤连两单示例表。清单1负之代码用用以创造这片个示例表。确保于用户数据数据库被运行这些本子,而非是于master中。

CREATE TABLE Product (ID int, 

CREATE TABLE Product (ID int, 

                      ProductName varchar(100),

                      ProductName varchar(100),

                      Cost money);CREATE TABLE SalesItem (ID int, 

                      Cost money);CREATE TABLE SalesItem (ID int, 

                        SalesDate datetime, 

                        SalesDate datetime, 

                        ProductID int, 

                        ProductID int, 

                        Qty int, 

                        Qty int, 

                        TotalSalesAmt money);INSERT INTO Product

                        TotalSalesAmt money);INSERT INTO Product

VALUES (1,’Widget’,21.99),

VALUES (1,’Widget’,21.99),

       (2,’Thingamajig’,5.38), 

       (2,’Thingamajig’,5.38), 

   (3,’Watchamacallit’,1.96);INSERT INTO SalesItem

   (3,’Watchamacallit’,1.96);INSERT INTO SalesItem

VALUES (1,’2014-10-1′,1,1,21.99),

VALUES (1,’2014-10-1′,1,1,21.99),

       (2,’2014-10-2′,3,1,1.96),

       (2,’2014-10-2′,3,1,1.96),

       (3,’2014-10-3′,3,10,19.60),

       (3,’2014-10-3′,3,10,19.60),

       (4,’2014-10-3′,1,2,43.98),

       (4,’2014-10-3′,1,2,43.98),

       (5,’2014-10-3′,1,2,43.98); 

       (5,’2014-10-3′,1,2,43.98); 

列表1:交叉连接的示例表

列表1:交叉连接的示例表

对第一只交叉连接示例,我以运行列表2负之代码。

于第一个交叉连接示例,我以运行列表2遇之代码。

SELECT * FROM 

SELECT * FROM 

Product CROSS JOIN SalesItem;

Product CROSS JOIN SalesItem;

列表2:简单的陆续连接示例

列表2:简单的穿插连接示例

当自家当一个SQL Server Management
Studio窗口中运作列表2受之代码时,通过自身之对话设置输出结果的文书,我赢得了晓1着之输出:

当自身当一个SQL Server Management
Studio窗口中运作列表2备受的代码时,通过自我之对话设置输出结果的公文,我获取了告知1遭受的出口:

ID  ProductName           Cost     ID   SalesDate
              ProductID Qty  TotalSalesAmt

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    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    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    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    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

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     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     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     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     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

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     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     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     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     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

3    Watchamacallit       1.96     5    2014-10-03 00:00:00.000 1
        2    43.98

报告1:运行列表2的结果

晓1:运行列表2的结果

如果您想起报告1的结果,你可看出15个不等的记录。这些前5独记录包含从成品表明底率先履及SalesItem表中5只不同之行连接的列值。同样适用于活表明底2秒和3行。返回的行数是Product表中之行数乘以SalesItem表中的行数,即15执行。

而您想起报告1的结果,你可看看有15只不同之笔录。这些前5独记录包含从活说明的首先执行与SalesItem表中5独不同之行连接的列值。同样适用于活说明的2秒和3行。返回的行数是Product表中之行数乘以SalesItem表中之行数,即15实施。

始建Cartesian产品可能有效的一个缘由是生成测试数据。假设我怀念在自家之出品与SalesItem表中利用日期变更有不比之制品。我可下一个接力连接来实现,如列表3所显示:

创办Cartesian产品或许有效的一个因是转测试数据。假设我怀念当自之制品及SalesItem表中运用日期变更有差之产品。我得以下一个接力连接来贯彻,如列表3所显示:

SELECT ROW_NUMBER() OVER(ORDER BY ProductName DESC) AS ID,

SELECT ROW_NUMBER() OVER(ORDER BY ProductName DESC) AS ID,

       Product.ProductName

       Product.ProductName

   + CAST(SalesItem.ID as varchar(2)) AS ProductName, 

   + CAST(SalesItem.ID as varchar(2)) AS ProductName, 

       (Product.Cost / SalesItem.ID) * 100 AS CostFROM Product
CROSS JOIN SalesItem;

       (Product.Cost / SalesItem.ID) * 100 AS CostFROM Product
CROSS JOIN SalesItem;

列表3:简单的接力连接示例

列表3:简单的交叉连接示例

当自身运行列表3饱受之代码时,我沾了告知2惨遭之出口。

当我运行列表3遭受的代码时,我收获了晓2受到的输出。

ID    ProductName                                                 Cost

ID    ProductName                                                 Cost





1     Widget1
                                                    2199.00

1     Widget1
                                                    2199.00

2     Widget2
                                                    1099.50

2     Widget2
                                                    1099.50

3     Widget3                                                     733.00

3     Widget3                                                     733.00

4     Widget4                                                     549.75

4     Widget4                                                     549.75

5     Widget5                                                     439.80

5     Widget5                                                     439.80

6     Watchamacallit1                                             196.00

6     Watchamacallit1                                             196.00

7     Watchamacallit2                                             98.00

7     Watchamacallit2                                             98.00

8     Watchamacallit3                                             65.33

8     Watchamacallit3                                             65.33

9     Watchamacallit4                                             49.00

9     Watchamacallit4                                             49.00

10    Watchamacallit5                                             39.20

10    Watchamacallit5                                             39.20

11    Thingamajig1                                                538.00

11    Thingamajig1                                                538.00

12    Thingamajig2                                                269.00

12    Thingamajig2                                                269.00

13    Thingamajig3                                                179.33

13    Thingamajig3                                                179.33

14    Thingamajig4                                                134.50

14    Thingamajig4                                                134.50

15    Thingamajig5                                                107.60

15    Thingamajig5                                                107.60

告2:运行列表3之结果

喻2:运行列表3的结果

透过翻看列表3负之代码,您得看,我老成了部分排列,其中饱含与制品表中的多少类的多寡。通过使用ROW_NUMBER函数,我可在每行上生成唯一的ID列。此外,我以SalesItem表中的ID列创建惟一的ProductName和成本列值。产生的行数等于产品表中的行数乘以SalesItem表中的行数。

透过翻列表3负之代码,您可以看,我生成了一部分列,其中蕴藏与产品表中的数类的数目。通过动ROW_NUMBER函数,我得于每行上生成唯一的ID列。此外,我用SalesItem表中的ID列创建惟一的ProductName和成本列值。产生的行数等于产品表中的行数乘以SalesItem表中的行数。

顶目前为止,本节蒙之演示只实行了跨越片单说明底穿插连接。可以利用CROSS
JOIN操作符跨多个说明执行交叉连接操作。列表4蒙之以身作则在三只说明中开创了一个Cartesian产品。

暨目前为止,本节遭遇的言传身教只实行了逾片单说明的交叉连接。可以应用CROSS
JOIN操作符跨多独说明执行交叉连接操作。列表4受的示范在三只说明中开创了一个Cartesian产品。

SELECT * FROM sys.tables CROSS JOIN sys.objectsCROSS JOIN sys.sysusers;

SELECT * FROM sys.tables CROSS JOIN sys.objectsCROSS JOIN sys.sysusers;

列表4:使用CROSS JOIN操作符创建三只说明的Cartesian产品

列表4:使用CROSS JOIN操作符创建三单说明底Cartesian产品

运作列表4的输出有半点个不同之CROSS_JOIN操作。由该代码创建的Cartesian产品将发生一个结出集,其母公司多次等于sys中之行数。表乘以sys中的行数。对象就以sysusers中的行数。

运行列表4的出口有半点独例外的CROSS_JOIN操作。由该代码创建的Cartesian产品以产生一个结实集,其母公司多次等于sys中的行数。表乘以sys中之行数。对象就以sysusers中之行数。

当交叉连接执行类似于内连接时

当交叉连接执行类似于内连接时

每当前的有些受,我关系了,当用交叉连接运算符时,它会生一个笛卡尔积。这不是真的的。当您使用WHERE子词约束连接至过连操作SQL Server的表时,不见面创笛卡尔出品。相反,它的功效相近于一般性的连操作。为了演示这种表现,请查看列表5惨遭的代码。

当前边的一对中,我关系了,当用交叉连接运算符时,它会发生一个笛卡尔积。这不是当真的。当你使用WHERE子句约束连接至跨连操作SQL Server的表时,不见面创笛卡尔活。相反,它的功力相近于平常的连日操作。为了演示这种表现,请查看列表5饱受之代码。

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

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

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

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

列表5:两只当价格的SELECT语句。

列表5:两个顶价格的SELECT语句。

列表5负之代码包含两独SELECT语句。第一独SELECT语句以CROSS JOIN操作符,然后使用WHERE子句定义如何连接至交叉连接操作中的片个说明。第二个SELECT语句以一个正常化的内连接操作符,并使用一个ON子句来连续这点儿单说明。SQL Server的询问优化器足够聪明,可以知道列表5受到的率先个SELECT语句可以看做其中连接重新编辑。优化器知道,当用交叉连接操作时,它可另行编排查询,与在交叉连接着提到的星星独说明中提供连接谓词之WHERE子句一起以。因此,SQL Server引擎为列表5遇之SELECT语句生成相同之实施计划。当你不提供一个束缚SQL服务器无亮堂怎么连接跨连操作的鲜个表时,它见面在和接力连接操作相关联的有限独聚众之间创造一个Cartesian产品。

列表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所展示:

以面前的小节中找到的以身作则是以救助你明白CROSS
JOIN操作符以及哪些用它们。使用CROSS JOIN操作符的一个效是运用她来扶持在一个表中查找和另一个表中没有匹配记录的宗。例如,假而自己眷恋使当自身之产品表明中各个一个产品于贩卖出底各级一个日期,报告自己之活表明中每个产品名称的究竟数量及总销售额。因为在我之例证中,每一个活之名字都不是每日还发销售,我之晓要求凡自己用出示一个0之数码和总的销售额的0美元,因为这些制品于某某平等天尚未销售。这是交叉连接操作符与左外JOIN操作的构成,它以帮我识别那些当加的等同天遭遇无给贩卖的色。满足这些报告要求的代码如列表6所展示:

SELECT S1.SalesDate, ProductName

SELECT S1.SalesDate, ProductName

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

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

 , ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSalesFROM Product
PCROSS JOIN  (SELECT DISTINCT SalesDate FROM SalesItem

 , ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSalesFROM Product
PCROSS JOIN  (SELECT DISTINCT SalesDate FROM SalesItem

  ) S1LEFT OUTER JOIN 

  ) S1LEFT OUTER JOIN 

SalesItem S2ON P.ID = S2.ProductIDAND S1.SalesDate
= S2.SalesDateGROUP BY S1.SalesDate, P.ProductNameORDER BY S1.SalesDate;

SalesItem S2ON P.ID = S2.ProductIDAND S1.SalesDate
= S2.SalesDateGROUP BY S1.SalesDate, P.ProductNameORDER BY S1.SalesDate;

列表6:查找无动交叉连接销售的活

列表6:查找无利用交叉连接销售的活

受自己带来您走过这段代码。我创建了一个子询问,它选择有不同之SalesDate值。这个子查询提供了有的日子,其中有一个销售。然后自己以她同我之制品说明连接起来。这允许自己以每个销售日期和每个产品执行以内创造一个Cartesian产品。从交叉连接返回的联谊将持有在末结果集中所用之享有值,除了每个产品的Qty和TotalSalesAmt的总和。为了取得这些聚集总值,我于SalesItem表上推行一个左外连接,并与通过CROSS JOIN操作创建的Cartesian产品接连。我因ProductID和SalesDate列执行了这个连续。通过应用自己之Cartesian产品受之左外联接来回到,如果产生一个跟ProductID和SalesDate相匹配的SalesDate记录,那么Qty和TotalSalesAmt值将和相应的行相关联。这个查询的最终一起事是采用GROUP BY子句来总基于SalesDate和ProductName的Qty和TotalSalesAmount。

被自己带你走过这段代码。我创建了一个子查询,它选择具有不同的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实行。这是一个格外挺的结果集,它将消费好多时光来创造它。

来笛卡尔积的交叉连接运算符有一些特性方面要考虑。因为SQL引擎需要以一个集中入每一样实施,而于另一个聚众中,结果集可以一对一好。如果自己做一个陆续连接一个表明有1,000,000实行与其余一个表出100,000推行那么自己的结果集就会见起1,000,000 X 10万实施,或者说100,000,000,000履。这是一个非常怪之结果集,它以花费很多年华来创造它。

陆续连接操作符可以是一个要命好之解决方案,可以以拥有可能的咬合被规定一个结果集,就比如拥有客户的每个月份之兼具销售,即使在几乎单月之年华里,一些客户无销售。在动用CROSS
JOIN操作符时,如果希望优化性能,应该尽量减少交叉联接的尺寸。例如,假要自己起一个发明,其中含有过去少单月之行销数目。如果本身想使特别成一个报,显示一个月没销售的客户,那么确定一个月的气数的章程可以极大地改变自己之询问的性。为了证明当时一点,我第一为1000称呼客户创造了一个期限半只月的销售记录。我将使用列表7遭受之代码来贯彻即一点。

接力连接操作符可以是一个非常好的化解方案,可以于具有可能的结被规定一个结实集,就像所有客户之每个月份之所有销售,即使在几乎个月之流年里,一些客户无销售。在使用CROSS
JOIN操作符时,如果要优化性能,应该尽量减少交叉联接的分寸。例如,假而自己产生一个阐明,其中蕴蓄过去简单个月之行销数量。如果自己眷恋要十分成一个语,显示一个月无销售的客户,那么规定一个月之天数的方可极大地改我之询问的性能。为了说明就或多或少,我先是为1000称客户创造了一个时限半个月的销售记录。我用用列表7吃的代码来贯彻这或多或少。

CREATE TABLE Cust (Id int, CustName varchar(20));CREATE TABLE Sales (Id
int identity

CREATE TABLE Cust (Id int, CustName varchar(20));CREATE TABLE Sales (Id
int identity

                    ,CustID int

                    ,CustID int

,SaleDate date

,SaleDate date

,SalesAmt money);SET NOCOUNT ON;DECLARE @I int = 0;DECLARE @Date
date;WHILE @I < 1000BEGIN

,SalesAmt money);SET NOCOUNT ON;DECLARE @I int = 0;DECLARE @Date
date;WHILE @I < 1000BEGIN

SET @I = @I + 1;

SET @I = @I + 1;

SET @Date = DATEADD(mm, -2, ‘2014-11-01’);

SET @Date = DATEADD(mm, -2, ‘2014-11-01’);

INSERT INTO Cust

INSERT INTO Cust

VALUES (@I, 

VALUES (@I, 

        ‘Customer #’ + right(cast(@I+100000 as varchar(6)),5));

        ‘Customer #’ + right(cast(@I+100000 as varchar(6)),5));

WHILE @Date < ‘2014-11-01’ 

WHILE @Date < ‘2014-11-01’ 

BEGIN

BEGIN

IF @I%7 > 0

IF @I%7 > 0

INSERT INTO Sales (CustID, SaleDate, SalesAmt) 

INSERT INTO Sales (CustID, SaleDate, SalesAmt) 

VALUES (@I, @Date, 10.00);

VALUES (@I, @Date, 10.00);

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

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

ENDEND

ENDEND

列表7:TSQL为性测试创建示范数据

列表7:TSQL为性测试创建示范数据

列表7饱受之代码为1000单例外之客户创造了一定量独月的多寡。这段代码没有吗各个7单客户有增无减销售数目。这段代码有了1000个Cust表记录以及52,338单销售表记录。

列表7被之代码为1000单例外之客户创造了一定量独月的数目。这段代码没有啊每7单客户有增无减销售数目。这段代码有了1000个Cust表记录以及52,338单销售表记录。

为了演示如何运用交叉连接操作符执行不一之操作,这在跨连输入集中采取的集结的分寸,让自己来利用队表8和列表9吃的代码。对于每个测试,我以记录返回结果所急需的时空。

为演示如何使交叉连接操作符执行不同的操作,这取决于跨连输入集中使用的集的大小,让自身来使队表8和列表9遭到的代码。对于每个测试,我用记录返回结果所需要的辰。

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

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 

       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

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:与富有销售记录交叉连接

列表8:与持有销售记录交叉连接

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

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 

       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

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:与差之行销日期列表交叉连接

列表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款款得差不多。

于列表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暂缓得多。

恰巧而您所看到底,交叉连接操作中应用的记录数可以大幅度地影响查询运行的流年长短。因此,如果你可以编写而的询问来最为小化交叉连接操作着干的笔录之数量,那么你的询问将推行得再有效率。

适使你所见到的,交叉连接操作着行使的记录数可以极大地震慑查询运行的流年长短。因此,如果您可以编制而的查询来最为小化交叉连接操作着干的记录的数据,那么您的查询将实行得又有效率。

结论

结论

交叉连接运算符在有限只记录集之间有一个笛卡尔积。这个操作符有助于识别一个表中没有和其它一个申明中相当的笔录的起。应注意尽量减少与接力连接操作符使用的记录集的高低。通过保险交叉连接的结果集尽可能小,您将包代码尽可能快地运转。

陆续连接运算符在简单单记录集之间时有发生一个笛卡尔积。这个操作符有助于识别一个表中没有跟其余一个发明中匹配的笔录之起。应小心尽量减少与接力连接操作符使用的记录集的轻重。通过保险交叉连接的结果集尽可能小,您将确保代码尽可能快地运转。

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图