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

尖端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舒缓得差不多。

正好使您所见到的,交叉连接操作着采取的记录数可以大幅度地震慑查询运行的岁月长。因此,如果您可编制而的查询来最小化交叉连接操作着涉嫌的笔录之数据,那么你的查询将实行得又有效率。

结论

交叉连接运算符在片单记录集之间产生一个笛卡尔积。这个操作符有助于识别一个表中没有与任何一个表明中相当的笔录的宗。应小心尽量减少与接力连接操作符使用的记录集的尺寸。通过保险交叉连接的结果集尽可能小,您将包代码尽可能快地运作。

相关文章