SQL Server翻译:高级t – sql第2级的阶梯:使用交叉连接来引进高档t – sql

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

发源:格雷戈里·拉森,二〇一五/02/1九(第二遍出版:贰零1陆/12/一7

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

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

 

正文:

系列

正文是阶梯种类的一片段:高级t – sql的阶梯

其一楼梯将富含1多重的篇章,这么些小说将扩充到你在前面包车型大巴多少个t

  • sql stairways中学习的t – sql基础上,以及在基础之上的t – sql DML和t
  • sql的台阶。那一个楼梯应该支持读者准备通过微软求证考试70 – 肆陆1:查询微软SQL Server 二零一三。

那是八个新的梯子类别的首先篇小说,它将探索Transact
SQL(TSQL)的越来越高级天性。这一个楼梯将含有壹层层的稿子,那几个文章将扩充到你在在此之前的多个TSQL stairways中读书的TSQL基础:

··t – sql DML楼梯

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

其1“高级Transact SQL”的梯子将富含以下TSQL主题:

··使用交叉连接操作符

··使用应用操作符

··领会公共表表明式(CTE)

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

··将使用主数据的支撑

··使用透视将列进行

··订购您的数据应用排序的效益

··管理日期和时间函数

··驾驭在条款的变型

以此楼梯的读者应当早就很好地领略了怎么着从SQL
Server表查询、更新、插入和删除数据。别的,他们理应有1个行事知识,那些方法能够用来决定他们的TSQL代码的流程,以及能够测试和操作数据。

这么些楼梯应该帮忙读者准备通过微软认证考试70

  • 四6一:查询微软SQL Server 2013。

在那几个新的阶梯类别的第三期中,笔者将钻探CROSS
JOIN操作符。

CROSS JOIN操作符介绍

穿插连接操作符能够将3个数据集的具备记录合并到另二个数目汇总的有所记录。通过使用两组记录之间的穿插连接操作符,您创制了3个叫作笛Carl乘积的东西。

那边有一个大约的例子,使用CROSS
JOIN操作符来连接多个表A和B:

 SELECT * FROM A CROSS JOIN B

瞩目,当使用交叉连接操作符时,未有连接子句连接八个表,就如在多少个表之间进行内部和外部连接操作时利用的连接子句。

亟需小心的是,使用交叉连接能够生成1个大的记录集。为了探讨那种行为,我们来看望七个不等的例子,表明那么些结果集的大小未来自于交叉连接操作。对于第一个示范,假使您是穿插连接五个表,个中表A有十行,表B有三行。一个交叉连接的结果集将是10加倍叁或30行。对于第2个示范,假诺表A有一千万行,表B有300万行。在表a和B之间的接力连接结果中有些许行?那将是一个壮烈的三千0亿行。那是大气的行,要求多量的年月和多量的能源来创立这几个结果集。因而,在大型记录集上利用交叉连接操作符时供给万分小心。

让大家仔细斟酌一下施用CROSS
JOIN操作符的局地例证。

行使交叉连接的着力示例

在前头的多少个例证中,大家将会再三再四三个示例表。清单第11中学的代码将用来成立那八个示例表。确认保障在用户数量数据库中运转那个本子,而不是在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:交叉连接的示例表

对此第3个交叉连接示例,小编将运维列表第22中学的代码。

SELECT * FROM 

Product CROSS JOIN SalesItem;

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

当作者在三个SQL Server Management
Studio窗口中运维列表第22中学的代码时,通过自个儿的对话设置输出结果的文本,作者获取了报告第11中学的输出:

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

告知一:运维列表2的结果

比方您想起报告一的结果,你能够看来有一四个例外的笔录。那么些前多个记录包括从产品表的率先行与SalesItem表中多少个例外的行连接的列值。同样适用于产品表的二秒和三行。重返的行数是Product表中的行数乘以SalesItem表中的行数,即15行。

开创Cartesian产品可能有效的八个缘由是生成测试数据。假若笔者想在本人的产品和SalesItem表中应用日期变更1些不等的成品。小编能够利用叁个交叉连接来促成,如列表叁所示:

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:简单的交叉连接示例

当笔者运维列表三中的代码时,作者获取了告知第22中学的输出。

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

报告贰:运营列表叁的结果

因而翻看列表叁中的代码,您可以见见,我生成了某个列,在那之中含有与产品表中的数码类似的数码。通过运用ROW_NUMBEPRADO函数,小编可以在每行上生成唯一的ID列。其它,小编使用SalesItem表中的ID列创造惟1的ProductName和财力列值。发生的行数等于产品表中的行数乘以SalesItem表中的行数。

到近期截止,本节中的示例只实行了跨七个表的交叉连接。能够行使CROSS
JOIN操作符跨多个表执行交叉连接操作。列表四中的示例在八个表中开创了1个Cartesian产品。

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

列表4:使用CROSS JOIN操作符创造多个表的Cartesian产品

运维列表4的出口有七个区别的CROSS_JOIN操作。由该代码创造的Cartesian产品将生出二个结出集,其母集团数等于sys中的行数。表乘以sys中的行数。对象乘以sysusers中的行数。

当交叉连接执行类似于在那之中连接时

在头里的片段中,笔者提到过,当使用交叉连接运算符时,它会发生三个笛Carl积。那不是真的。当您使用WHERE子句约束连接到跨连接操作SQL Server的表时,不会创设笛Carl产品。相反,它的效果看似于平常的连天操作。为了演示那种行为,请查看列表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子句定义如何连接到交叉连接操作中的三个表。第3个SELECT语句使用叁个健康的中间连接操作符,并动用一个ON子句来连接那多个表。SQL Server的询问优化器充分聪明,能够清楚列表5中的第一个SELECT语句能够当作内部连接重新编排。优化器知道,当使用交叉连接操作时,它可以再一次编写查询,与在6续连接中关系的八个表之间提供连接谓词的WHERE子句一起利用。因而,SQL Server引擎为列表第55中学的SELECT语句生成相同的实施安插。当您不提供一个束缚SQL服务器不掌握怎样连接跨连接操作的四个表时,它会在与接力连接操作相关联的八个汇集之间成立一个Cartesian产品。

利用交叉连接查找未销售的制品

在头里的小节中找到的以身作则是为了帮扶你通晓CROSS
JOIN操作符以及如何利用它。使用CROSS JOIN操作符的一个效应是应用它来增加帮衬在1个表中查找与另1个表中未有相称记录的项。例如,即使自身想要在作者的制品表中每1个产品被售出的每个日子,报告自个儿的出品表中种种产品名称的总数量和总销售额。因为在自小编的事例中,每贰个出品的名字都不是天天都有销售,我的报告须求是自己需求体现二个0的多寡和总的销售额的0欧元,因为那么些产品在某壹天未有销售。那是交叉连接操作符与左外JOIN操作的结缘,它将扶持本人识别那一个在给定的一榴月从未被贩卖的品种。餍足这一个告诉必要的代码如列表六所示:

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;

列表陆:查找不利用交叉连接销售的产品

让自己带你度过这段代码。小编成立了1个子询问,它选取具有差异的SalesDate值。那一个子查询提供了拥有的日子,个中有多个售货。然后自个儿将它与本身的制品表连接起来。那允许小编在每一个销售日期和种种产品行期间创设多少个Cartesian产品。从交叉连接再次回到的集结将持有在最终结出集中所急需的具有值,除了每种产品的Qty和TotalSalesAmt的总额。为了获取那些汇总值,作者在SalesItem表上执行一个左外连接,并与通过CROSS JOIN操作创立的Cartesian产品连续。笔者依据ProductID和SalesDate列执行了此一而再。通过应用自家的Cartesian产品中的左外联接来回到,若是有一个与ProductID和SalesDate相匹配的SalesDate记录,那么Qty和TotalSalesAmt值将与相应的行相关联。那么些查询的末梢壹件事是利用GROUP BY子句来计算基于SalesDate和ProductName的Qty和TotalSalesAmount。

性子思考

发生笛Carl积的交叉连接运算符有一些特性方面须要思念。因为SQL引擎需求在二个成团中投入每1行,而在另多少个集合中,结果集能够一对一大。若是本人做3个接力连接四个表有壹,000,000行和另二个表有十0,000行那么作者的结果集就会有①,000,000 X 八万行,或许说100,000,000,000行。这是2个不小的结果集,它将花好多岁月来创制它。

交叉连接操作符能够是二个很好的消除方案,能够在富有希望的结缘中鲜明三个结出集,就像是拥有客户的各样月的拥有销售,即便在多少个月的岁月里,1些客户未有销售。在选取CROSS
JOIN操作符时,即使指望优化质量,应该尽量缩短交叉联接的大小。例如,即便自身有三个表,当中蕴蓄过去八个月的行销数量。即使自个儿想要生成二个告诉,突显贰个月未有销售的客户,那么分明三个月的天数的不2秘籍能够大幅地转移自我的查询的性情。为了印证那或多或少,笔者首先为一千名客户创制了1个年限八个月的行销记录。笔者将采取列表七中的代码来贯彻那或多或少。

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个Cust表记录和5二,33十五个销售表记录。

为了演示怎么样选用交叉连接操作符执行不一的操作,那取决跨连接输入集中采纳的联谊的轻重,让自身来运维列表8和列表玖中的代码。对于种种测试,笔者将记录再次回到结果所需的小时。

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

列表八:与具有销售记录交叉连接

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:与分化的销售日期列表交叉连接

在列表第88中学,CROSS JOIN操作符参加了一千个Cust记录,其中有5贰,33四个销售记录,生成2个创纪录的52338000行的记录集,然后用来鲜明3个月销售为零的客户。在列表九中,笔者将精选正式从Sales表中改变为只回去壹组不相同的SalesDate值。这几个奇异的集结只产生了62个不等的销售日期值,由此列表玖中的CROSS JOIN操作的结果只爆发了陆一,000条记下。通过压缩交叉连接操作的结果集,清单玖中的查询运维不到一秒,而列表第88中学的代码在本身的机械上运转了1玖秒。那种性质差距的显要缘由是记录SQL Server须要处理每一种查询执行的例外操作的数目。假若您查看八个清单的执行陈设,您将见到安排略有分歧。可是,假设您看一下嵌套循环(Inner Join)操作所生成的笔录的数据,在图形化安顿的右侧,您将见到列表8估量有5233八千条记下,而列表九中的操作仅推断有六1,000条记下。这一个英豪的记录集,列表8的查询布署从交叉连接嵌套循环操作中生成,然后再传递到多少个附加的操作。因为列表第88中学的全数操作都必须处理5200万的笔录。列表八比列表9慢得多。

正如你所见到的,交叉连接操作中接纳的记录数能够非常的大地影响查询运转的光阴长短。因而,若是你能够编写您的查询来最小化交叉连接操作中提到的记录的数量,那么你的询问将实施得更有作用。

结论

陆续连接运算符在多个记录集之间产生三个笛卡尔积。这么些操作符有助于识别2个表中未有与另二个表中相称的笔录的项。应留神尽量减弱与接力连接操作符使用的记录集的高低。通过担保交叉连接的结果集尽或然小,您将确定保证代码尽恐怕快地运作。

相关文章