当前位置: 澳门新濠3559 > 数据库 > 正文

SELECT字句技术澳门新濠3559,关联子查询是子查询

时间:2019-11-22 23:13来源:数据库
很多SQLServer程序员对子查询的使用感到困惑,尤其对于嵌套子查询。现在,就让我们追本溯源地探究这个问题。有两种子查询类型:标准和相关。标准子查询执行一次,结果反馈给父查

很多SQLServer程序员对子查询的使用感到困惑,尤其对于嵌套子查询。现在,就让我们追本溯源地探究这个问题。 有两种子查询类型:标准和相关。标准子查询执行一次,结果反馈给父查询。相关子查询每行执行一次,由父查询找回。在本文中,我将重点讨论嵌套子查询。 试想这个问题:你想生成一个卖平垫圈的销售人员列表。你需要的数据分散在四个表格中:人员.联系方式,人力资源.员工(HumanResources.Employee),销售.销售订单标题(Sales.SalesOrderHeader),销售.销售订单详情(Sales.SalesOrderDetail)。在SQLServer中,你从内压式写程序,但从外压式开始考虑非常有帮助,即可以一次解决需要的一个语句。 如果从内到外写起,可以检查Sales.SalesOrderDetail表格,在LIKE语句中匹配产品数值。你将这些行与Sales.SalesOrderHeader表格连接,从中可以获得销售人员IDs。然后使用SalesPersonID连接SalesPersonID表格。最后,使用ContactID连接Person.Contact表格。 复制代码 代码如下:USEAdventureWorks; GO SELECTDISTINCTc.LastName,c.FirstName FROMPerson.ContactcJOINHumanResources.Employeee ONe.ContactID=c.ContactIDWHEREEmployeeIDIN (SELECTSalesPersonID FROMSales.SalesOrderHeader WHERESalesOrderIDIN (SELECTSalesOrderID FROMSales.SalesOrderDetail WHEREProductIDIN (SELECTProductID FROMProduction.Productp WHEREProductNumberLIKE'FW%'))); GO这个例子揭示了有关SQLServer的几个绝妙事情。你可以发现,可以用IN()参数替代SELECT语句。在本例中,有两次应用,因此创建了一个嵌套子查询。 我是标准化的发烧友,尽管我不接受其荒谬的长度。由于标准化具有各种查询而增加了复杂性。在这些情况下子查询就显得非常有用,嵌套子查询甚至更加有用。 当你需要的问题分散于很多表格中时,你必须再次将它们拼在一起,这时你可能发现嵌套子程序就很有用。

   9.1:子查询类型

列列表中的子查询

列列表中的子查询是SELECT语句,它返回放置在SELECT子句的列列表中的单个列值。 为了演示如何在选择列表中使用子查询,我们假设我们必须从具有以下业务需求的SELECT语句生成一个结果集:

  • 返回所有Sales.SalesOrderHeader记录有什么有OrderDate等于“2007-02-19 00:00:00.000”
  • 通过SalesOrderID命令返回的记录
  • 编号每行返回的最旧的顺序的RowNumber为1,next oldest的RowNumber为2等
  • 结果集需要一个名为TotalOrders的列,需要使用等于“2007-02-19 00:00:00.000”的OrderDate的总订单数量进行填充

清单1中列出了满足这些要求的代码。

SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) RowNumber
      , (SELECT COUNT(*) 
         FROM [Sales].[SalesOrderHeader] 
         WHERE ModifiedDate = '2007-02-19 00:00:00.000') 
                     AS TotalOrders
      , *
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '2007-02-19 00:00:00.000';

InvoicePrice decimal (7,2));

    在 Management Studio 工具栏上,单击“文件”,指向“打开”,然后单击“文件”

什么是子查询?

子查询只是一个SELECT语句,它包含在另一个Transact-SQL语句中。可以在任何可以使用表达式的地方使用子查询。许多子查询返回单个列值,因为它们与比较运算符(=,!=,<,<=,>,> =)或表达式结合使用。当子查询不用作表达式或使用比较运算符时,它可以返回多个值。此外,子查询甚至可以在FROM子句或关键字EXISTS中使用时返回多个列和值。

子查询容易在Transact-SQL语句中发现,因为它将是括号中的SELECT语句。由于子查询包含在Transact-SQL语句中,因此子查询通常称为内部查询。而包含子查询的Transact-SQL语句被称为外部查询。子查询的另一个特点是可以独立于外部查询运行,并且将无错误地运行,并且可能返回一组行或空行集。

子查询的另一种形式是相关子查询。但是相关的子查询不能独立于外部的Transact SQL语句运行。相关子查询使用外部查询中的列或列来约束从相关子查询返回的结果。这对于本文的相关子查询足够了。我将在未来的楼梯文章中探索相关的子查询。

使用子查询时还需要考虑以下几点:

  • ntext,text和image数据类型不允许从子查询返回
  • ORDER BY子句不能用于子查询,除非使用TOP操作符
  • 使用子查询的视图无法更新
  • COMPUTE和INTO子句不能在子查询中使用

         WHERE SalesOrderID = OH.SalesOrderID

   

问题2:

什么时候子查询只需要一个列和值才能返回(选择所有适用的)?

  • 当子查询用于FROM子句时
  • 当IN子句中使用子查询时
  • 当表达式中使用子查询时
  • 当子查询与比较运算符一起使用时
SELECT CarName, InvoicePrice/StickerPrice*100.0 AS InvoicePriceRatio 

FROM CarInventory;

     本系列笔记均基于AdventureWorks数据库,有关AdventureWorks的安装帮助如下:

问题1:

正确的答案是c。子查询可以独立于外部查询运行,并返回结果。它不需要来自外部查询的任何列,如果它有来自外部查询的列,它将被称为相关子查询。

在HAVING子句中关联子查询的示例

    联接总是可以表示为子查询。子查询经常(但不总是)可以表示为联接。这是因为联接是对称的:无论以何种顺序联接表 A 和 B,都将得到相同的结果。而对子查询来说,情况则并非如此。

关于系列

本文属于进阶系列:T-SQL进阶:超越基础

跟随Gregory Larsen的T-SQL DML进阶系列,其涵盖了更多的高级方面的T-SQL语言,如子查询。

在您开始创建超出基本Transact-SQL语句的更复杂的SQL代码时,您可能会发现需要使用其他SELECT语句的结果来限制查询。 当在父Transact-SQL语句中嵌入SELECT语句时,这些嵌入式SELECT语句被称为子查询或相关子查询。 在“超越基础”楼梯的这个层次上,我将讨论一个子查询的不同方面,在将来的一个层面上,我将讨论相关的子查询。

        相关子查询可以执行很多次。它将为在外部查询中选择的每个候选行运行一次。每个候选行的列值将用于为关联子查询的每次执行的内部的外部查询列提供值。包含相关子查询的语句的最终结果将基于相关子查询的每次执行的结果。

1:安装AdventureWorks

清单5:HAVING子句中的子查询

清单5中的代码具有HAVING子句右侧的子查询,并在我的子查询中使用COUNT函数来确定“2006-05-01”上的订单数量。

SET NOCOUNT ON;

4:like

返回多个值的子查询的示例

我迄今为止的所有示例都包含仅在单个列中返回单个值的子查询。 并不是所有的子查询都有这个要求。 接下来的几个例子将使用返回多个值和/或多个列的子查询。

GO

     联接可分为以下几类:

清单12:测试清单3和清单4的性能代码

在运行列表12中的代码之后,我回顾了“SET STATISTICS”语句生成的消息。 通过查看统计信息,我发现这两个查询对SalesOrderDetail表都有3,309个逻辑读取,对于Product表有两个逻辑读取,每个使用31 ms的CPU。 另外我查看了SQL Server为这两个查询创建的执行计划。 我发现SQL Server为两者生成了相同的执行计划。 因此,对于我的情况使用子查询或JOIN查询产生了等效的性能,正如微软所记录的那样。

        在这个第二阶梯,我讨论了如何在翻译T- SQL语句中使用子查询。这个阶梯将通过讨论一种称为关联子查询的子查询类型来扩展子查询主题。我将探讨什么是相关子查询,以及它与普通子查询的区别。此外,我还将为您提供一些超越基础的事务- sql语句示例,并使用关联子查询来帮助识别结果集中返回的行,以满足复杂的业务需求。

SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name ;

使用具有IN关键字的子查询的示例

您可以编写一个返回列的多个值的子查询的地方是当您的子查询生成与IN关键字一起使用的记录集时。 清单9中的代码演示了如何使用子查询将值传递给IN关键字。

SELECT * FROM [Sales].[SalesOrderDetail] 
WHERE ProductID IN 
        (SELECT ProductID 
         FROM [Production].[Product]
         WHERE Name like '%XL%');

              AND YEAR(Inner_H.[OrderDate]) = '2008') > 150000

     TABLESAMPLE 子句将从 FROM 子句中的表返回的行数限制到样本数或行数的某一百分比。例如:

问题3:

正确答案是错误的。 SQL Server优化器非常聪明,很可能为两个等效查询计算相同的执行计划。如果包含子查询的查询的执行计划和没有子查询的查询的执行计划最终都具有相同的执行计划,则两个查询将具有相同的性能。

29913       186387.5613           18638.756130

SELECT CustomerID, Name, SalesPersonID, Demographics
INTO  Store_Archive
FROM Sales.Store

By Gregory Larsen, 2016/01/01 (首次发布于: 2014/01/29)

清单8中的代码使用关联子查询中的外部查询的CarName来标识每个惟一的CarName的最大StickerPrice。然后,在相关子查询中发现的最大StickerPrice值用于更新具有相同名称的每个CarInventory记录的StickerPrice值。

    结果:(701 行受影响)

子条款示例

为了演示在HAVING子句中使用子查询,假设您具有以下业务要求:

生成包含Sales.SalesOrderHeader.OrderDate和每个日期的订单数量的结果集,其中订单数量超过“2006-05-01”上执行的订单数量。

为了满足这个要求,我开发了清单6中使用HAVING子句中的子查询的查询。

SELECT count(*), OrderDate 
FROM [Sales].[SalesOrderHeader]
GROUP BY OrderDate
HAVING count(*) >
       (SELECT count(*) 
        FROM [Sales].[SalesOrderHeader]
        WHERE OrderDate = '2006-05-01 00:00:00.000');

         WHERE SalesOrderID = OH.SalesOrderID) > 70;

     以下示例说明如何使用此增强功能。此查询找出所有山地车产品的价格、平均价格以及两者之间的差价。

问题1:

完成这个句子“一个子查询是另一个Transact-SQL语句中的SELECT语句,_____________________”。

  • 不能独立于完整的查询运行。
  • 引用来自外部查询的列。
  • 当独立于外部查询运行时,它将返回结果。

SELECT CustomerID FROM Sales.SalesOrderHeader OH

8.3使用INTO字句

清单9:使用子查询将值传递给IN关键字

清单9中的代码使用一个子查询从Product.Product表中返回不同的ProductID值,其名称包含字符“XL”。 然后在IN关键字中使用从子查询返回的这些ProductID值来约束从Sales.SalesOrderDetail表返回哪些行。

相关子查询的性能考虑

  • 内部联接(典型的联接运算,使用类似于 = 或 <> 的比较运算符)。内部联接包括同等联接和自然联接。
    内部联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行。例如,检索 studentscourses 表中学生标识号相同的所有行。
  • 外部联接。外部联接可以是左向外部联接、右向外部联接或完整外部联接。
    SELECT字句技术澳门新濠3559,关联子查询是子查询的一种形式。在 FROM 子句中可以用下列某一组关键字来指定外部联接:
    • LEFT JOIN 或 LEFT OUTER JOIN。
      左向外部联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某一行在右表中没有匹配行,则在关联的结果集行中,来自右表的所有选择列表列均为空值。
    • RIGHT JOIN 或 RIGHT OUTER JOIN
      右向外部联接是左向外部联接的反向联接。将返回右表的所有行。如果右表的某一行在左表中没有匹配行,则将为左表返回空值。
    • FULL JOIN 或 FULL OUTER JOIN
      完整外部联接将返回左表和右表中的所有行。当某一行在另一个表中没有匹配行时,另一个表的选择列表列将包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
  • 交叉联接
    交叉联接将返回左表中的所有行。左表中的每一行均与右表中的所有行组合。交叉联接也称作笛卡尔积。
清单6:函数调用中的子查询

清单6中的代码有两个不同的子查询。 两个子查询返回Sales.SalesOrderHeader表中的最大OrderDate。 但是第一个子查询用于将日期传递给DATEDIFF函数的第二个参数。

WHERE YEAR(Outer_H.[OrderDate]) = '2008'

 

清单3:WHERE子句中的子查询

清单3中的子查询位于WHERE条件的右侧。 此子查询标识Product.Product记录的ProductID,其中产品名称为“Long-Sleeve Logo Jersey,XL”。 此子查询允许我找到具有与“Long-Sleeve Logo Jersey,XL”的产品名称相关联的ProductID的所有Sales.SalesOrderDetail记录。

29923       220496.658            22049.665800

     以下查询将查找作为销售人员的雇员的姓名。

问题和答案

在本节中,您可以通过回答以下问题来查看您使用子查询概念了解的内容。

我得到了报告2中显示的错误,因为关联子查询包含对列的引用。SalesOrderID,它是来自外部查询的一列。由于所有相关子查询从外部查询引用一个或多个列,因此不能独立地运行与它关联的外部查询。不能独立于整个翻译T-SQL语句运行子查询的事实是将相关子查询与普通子查询区分开来。

T-SQL笔记1:SELECT及SELECT高级应用

子查询和JOIN之间的性能考虑

如果您已阅读由Microsoft生成的“子查询基础知识”文档(http://technet.microsoft.com/....aspx)),那么您可能已经在此语句中运行 包含子查询的语句的性能:

“在Transact-SQL中,包含子查询的语句和不具有语义相似的版本的语句通常没有性能差异。

要将使用子查询的查询的性能与不使用子查询的等效查询进行比较,我将在清单3中重写我的子查询以使用JOIN操作。 清单11显示了我重写的JOIN查询,相当于清单3中的查询。

SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = 'Long-Sleeve Logo Jersey, XL';

30048

  • 所有查询中的列数和列的顺序必须相同。
  • 数据类型必须兼容。

问题2:

正确的答案是c和d。当用作表达式或在比较操作中时,子查询需要返回一个列值。当子查询与IN关键字一起使用时,它可以返回列的单个或多个值。如果在FROM子句中使用子查询,它只能返回一列和一个值,但也可以返回多个列和值。

问题3:

    select top 10 from …

清单4:TOP子句中的子查询

清单4中的代码使用从子查询返回的OrderQty值来标识将在TOP子句中使用的值。 通过使用子查询来控制TOP子句返回的行数,可以构建一个子查询,以便在运行时动态地识别从查询返回的行数。

当我运行清单5中的代码时,我得到了报告3中的结果

12:TABLESAMPLE

在函数调用中使用子查询的示例

要演示在函数调用中使用子查询,假设您需要显示OrderDate和每个Sales.SalesOrderHeader记录的最大OrderDate之间的天数。 清单6中的代码符合此要求。

SELECT SalesOrderID
      , OrderDate
      ,DATEDIFF
          (
            dd,OrderDate
        ,(SELECT MAX(OrderDate)
          FROM [Sales].[SalesOrderHeader])
          ) AS DaysBetweenOrders
         ,(SELECT MAX(OrderDate)
        FROM [Sales].[SalesOrderHeader]) 
            AS MaxOrderDate
FROM [Sales].[SalesOrderHeader];

('Tahoe','TAH201409674A',52001.08,46000.01);

   7.1:GROUP BY ALL

清单1:列列表中的子查询

在这个单一的Transact-SQL语句中,您会看到两个不同的SELECT子句。 子查询是嵌入在清单1中的语句中间的SELECT语句,它在它周围有括号。 我已经删除了子查询语句,并将其放在清单2中,以防您想要测试以验证它可以独立于完整的Transact-SQL语句运行。

SELECT COUNT(*) 
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '2007-02-19 00:00:00.000'

T-SQL的阶梯:超越第三基本级别:构建相关的子查询

    结果:将会返回17条语句。

总结

子查询是嵌入另一个Transact-SQL语句的SELECT语句。子查询可以独立于外部查询运行,因此有时也称为独立查询。记住,任何时候你有一个子查询代替一个表达式,或者与比较运算符一起使用,它只能返回一个列和值。通常可以使用JOIN逻辑重写子查询。子查询是帮助您构建更复杂的Transact-SQL语句以满足业务需求的强大工具。

在本节中,您可以通过回答以下问题来回顾您如何理解相关子查询的概念。

7:GROUP BY

清单7:FROM子句中的子查询
  • 清单7中的代码使用FROM子句中的子查询来创建一个名为Last10SalesOrders的表别名。 我的子查询返回包含ProductID为716的最后10个Sales.alesOrderDetail记录。
  • 清单7中的代码是一个非常简单的例子,说明如何在FROM子句中使用子查询。 通过在FROM子句中使用子查询,您可以轻松地构建更复杂的FROM语法,该语法将子查询的结果与其他表或其他子查询相结合,如清单8所示。
    SELECT DISTINCT OrderDate
    FROM (SELECT TOP 10 SalesOrderID 
          FROM [Sales].[SalesOrderDetail]
          WHERE ProductID = 716
          ORDER BY ModifiedDate DESC) AS Last10SalesOrders
    JOIN [Sales].[SalesOrderHeader] AS SalesOrderHeader
    ON Last10SalesOrders.SalesOrderID = SalesOrderHeader.SalesOrderID
    ORDER BY OrderDate

WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail

SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (100 ROWS) ;

在修改数据的语句中使用子查询的示例

到目前为止,我的所有示例一直在演示如何在SELECT语句的不同部分中使用子查询。 也可以在INSERT,UPDATE或DELETE语句中使用子查询。 清单10中的代码显示了如何在INSERT语句中使用子查询。

DECLARE @SQTable TABLE (
OrderID int,
OrderDate datetime,
TotalDue money,
MaxOrderDate datetime);

-- INSERT with SubQuery
INSERT INTO @SQTable 
   SELECT SalesOrderID,
          OrderDate, 
          TotalDue, 
          (SELECT MAX(OrderDate) 
           FROM [Sales].[SalesOrderHeader]) 
   FROM [Sales].[SalesOrderHeader]
   WHERE CustomerID = 29614;

-- Display Records
SELECT * FROM @SQtable;

相关子查询不仅可以使用SELECT语句返回结果集。您还可以使用它们来更新SQL Server表中的数据。为了演示这一点,我首先使用清单4中的代码在tempdb表中生成一些测试数据。

   7.2:HAVING

WHERE子句中子查询的示例

有时你想根据SELECT语句的结果来驱动WHERE子句条件。 当您在WHERE子句中的SELECT语句时,此SELECT语句实际上是一个子查询。 要演示在WHERE子句中使用子查询,假设您需要显示包含购买超大型长袖徽标运动衫的Sales.SalesOrderDetail记录。 清单3中的代码通过使用子查询来满足我的显示要求。

SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM [Production].[Product]
                    WHERE Name = 'Long-Sleeve Logo Jersey, XL'); 

    在编写包含相关子查询的transact - sql语句时,应该注意一些性能方面的考虑。当外部查询包含少量行时,性能并不差。但是,当外部查询包含大量的行时,从性能的角度来看,它的伸缩性并不好。这是因为要对外部查询中的每个候选行执行相关的子查询。因此,当外部查询包含越来越多的候选行时,一个相关的子查询必须多次执行,因此transact

SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN ( SELECT SalesOrderID
    FROM Sales.SalesOrderDetail
    WHERE UnitPrice BETWEEN 1000 AND 2000) d ON
    s.SalesOrderID = d.SalesOrderID

清单11:与清单3中的查询相当的JOIN查询

要比较使用子查询的清单3中的查询的性能和使用JOIN的清单11中的查询,我将使用清单12中的代码运行两个查询。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Listing 3 query
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM Production.Product
                    WHERE Name = 'Long-Sleeve Logo Jersey, XL'); 

-- Listing 11 query
SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = 'Long-Sleeve Logo Jersey, XL';

问题和答案

  • 使用别名。有关详细信息,请参阅使用别名的子查询.aspx)。
  • 使用 IN 或 NOT IN。有关详细信息,请参阅使用 IN 的子查询.aspx)和使用 NOT IN 的子查询.aspx)。
  • 在 UPDATE、DELETE 和 INSERT 语句中。有关详细信息,请参阅 UPDATE、DELETE 和 INSERT 语句中的子查询.aspx)。
  • 使用比较运算符。有关详细信息,请参阅使用比较运算符的子查询.aspx)。
  • 使用 ANY、SOME 或 ALL。有关详细信息,请参阅用 ANY、SOME 或 ALL 修改的比较运算符.aspx)。
  • 使用 EXISTS 或 NOT EXISTS。有关详细信息,请参阅使用 EXISTS 的子查询.aspx)和使用 NOT EXISTS 的子查询.aspx)。
  • 代替表达式。有关详细信息,请参阅用于替代表达式的子查询.aspx)。

使用子查询来控制TOP条款的示例

使用TOP子句返回的行数可以由表达式控制。 清单5中的代码标识了应该根据TOP子句中的子查询返回的Sales.SalesOrderDetail行的数量。

SELECT TOP (SELECT TOP 1 OrderQty 
            FROM [Sales].[SalesOrderDetail]
            ORDER BY ModifiedDate) *  
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 716;

答案

7:GROUP BY

返回单个值的子查询的示例

如上所述,在表达式中使用的子查询或返回比较运算符一侧的值需要返回单个值。 Transact-SQL语句中有许多不同的地方,需要一个子查询来返回单个列值,例如在选择列表中WHERE子句等。在本节中,我将提供一系列示例,演示如何使用子查询 作为表达式或与比较运算符以满足不同的业务需求。

     , SUM(Outer_H.[SubTotal]) AS TotalPurchase

FirstName                                          LastName
-------------------------------------------------- ----------
Barry                                              Johnson
David                                              Johnson
Willis                                             Johnson
(3 row(s) affected)

 

11:联接

   通过联接,可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。

   联接条件中用到的列不必具有相同的名称或相同的数据类型。但如果数据类型不相同,则必须兼容,或者是可由 SQL Server 进行隐式转换的类型。

问题3:

在WHERE子句中使用一个子查询的Transact-SQL语句总是比不包含子查询(True或False)的等效查询执行得慢。

正确答案是c .相关子查询需要在相关子查询语句中使用外部查询中的一个或多个列。在执行相关子查询时,这些外部列引用将替换为每个候选行的值。

SELECT CustomerID, Name, SalesPersonID, Demographics
INTO  Store_Archive
FROM Sales.Store
WHERE 1=0

子查询示例数据示例

为了演示如何使用子查询,我将需要一些测试数据。 而不是创建自己的测试数据,我的所有示例都将使用AdventureWorks2008R2数据库。 如果您想跟随并在环境中运行我的示例,那么您可以从这里下载AdventureWorks2008R2数据库:http://msftdbprodsamples.code...

问题2

 

清单8:使用实际表连接派生表

在清单8中,我看到了我在清单7中创建的子查询/派生表,并将其与SalesOrderHeader表相加。 通过这样做,我可以确定最后10次订购ProductID = 716的OrderDate。

  1. 随着候选行的数量增加,包含相关子查询的transact - sql语句的性能得到了提高。
  2. 相关子查询将对来自外部查询的每个候选行执行一次。
  3. 相关子查询将引用内部查询中的一个或多个列。
  4. 当使用一个相关子查询在HAVING子句内的查询将被执行一次的每个候选行由GROUP BY子句。

8.1:使用DISTINCT消除重复值

清单10:INSERT语句中的子查询

在清单10中的代码中,我使用一个子查询来计算要插入列MaxOrderDate的值。 这只是在INSERT语句中如何使用子查询的一个示例。 请记住,也可以在UPDATE和/或DELETE语句中使用子查询。

如果我运行清单2中的代码,我将发现在报告2中显示了一个错误。

    where name like ‘b/B%’ escape ‘/’

FROM子句中的子查询示例

在FROM子句中,通常会标识您的Transact-SQL语句将对其执行的表或表的集合。 每个表提供一组记录,您的查询将用于确定查询的最终结果集。 子查询可以被认为是返回一组记录的查询,因此它可以像FROM表一样在FROM子句中使用。 清单7中的查询显示了我如何在FROM子句中使用子查询。 当在FROM子句中使用子查询时,从子查询生成的结果集通常称为派生表。

SELECT SalesOrderID 
FROM (SELECT TOP 10 SalesOrderID 
      FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = 716
      ORDER BY ModifiedDate DESC) AS Last10SalesOrders;

29641       210647.4929           21064.749290

8:SELECT字句技术

清单2:清单1中的子查询语句

通过将此子查询列在列列表中,清单1中的此Transact-SQL语句可以对OrderDate为“2007-02-19 00:00:00.000”的SalesOrderHeader行的数量进行计数,并将该信息与详细信息一起返回 有关具有相同OrderDate值的Sales.SalesOrderHeader记录的行信息。

    正确的答案是b和d .

5:escape

回答:

      有时,您可能想要从外部查询中限制一个有不同值的子句。这时,您可以在您的“HAVING”子句中使用相关子查询。假设您必须编写一个查询,该查询将计算那些在2008年税前购买价值超过15万美元的产品的客户的回扣金额。清单3中的代码通过在HAVING子句中使用关联子查询来计算这些值客户的回扣金额。

    使用:

('Grand Cherokee','JGC20141234556W',44518.31,36201.86),

     相当于对GROUP之前的查询内容进行再一次的条件检索。

29617       187964.844            18796.484400

 

问题2:

7.2:HAVING

报告3:运行清单3的结果

 

选择所有关于相关子查询的语句。

    不行吗?答案是不行。结果会返回0。

问题1

    解析:表示全部以‘b/B’开头的name,其中/不理解为通配符。

  1. 来自内部查询的一个或多个列,用于约束相关子查询的结果。
  2. 在相关子查询的选择列表中使用的内部查询中的一个或多个列。
  3. 来自外部查询的一个或多个列,用于约束相关子查询的结果。
  4. 在相关子查询的选择列表中使用的外部查询的一个或多个列。
USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT * 
 FROM HumanResources.Employee AS b
 WHERE a.ContactId = b.ContactID
 AND a.LastName = 'Johnson');
GO

" could not be bound.

6:TOP

FROM [Sales].[SalesOrderHeader] AS Outer_H

    以下是其中任一查询的结果集。

        , SUM(Outer_H.[SubTotal]) * .10 AS Rebate

     存在如下通配符,

  • sql语句将需要更长的时间运行。如果您发现相关子查询transact - sql语句的性能不符合您的要求,那么您应该寻找替代解决方案,例如使用内部或外部连接操作的查询,或者从外部查询返回少量候选行的查询。

 

当我运行清单1中的代码时,我得到了报告1中的输出。

     子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。

问题1:

DECLARE @Shifts varchar(20)
SET @Shifts = ''
SELECT @Shifts = @Shifts + s.Name + ',' FROM HumanResources.Shift s
SELECT @Shifts

报告2:在清单2中运行代码时出错

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO

清单1:在WHERE子句中关联子查询

SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (10 PERCENT) ;

29712

 

清单4中的代码创建了一个CarInventory表,然后填充了8行,表示当前库存的汽车。

    或者:

INSERT INTO CarInventory VALUES ('Explorer','EXP2014123456A',46198.45,38201.87),

9.1:子查询类型

        WHERE Inner_H.[CustomerID] = Outer_H.[CustomerID]

 

      为了演示在WHERE子句中使用关联子查询,我想要确定这些CustomerID在单个订单中购买了超过70个项目。为了达到这个要求,我可以运行清单1中的代码。

 

USE tempdb;

    INTO字句用来创建新表(对我来说便是备份数据)。

The multi-part identifier "OH.SalesOrderID

运算符 描述
!=
!>
!<
<
<=
<>
=
>
>=
ALL 比较标量值和单列集中的值。
ANY 比较标量值和单列集中的值。SOME 和 ANY 是等效的
BETWEEN 自动根据SQL的型别进行取值
CONTAINS 为单词或短语执行模糊搜索
ESCAPE 指定要以字面值形式搜索,而不是被解释为通配符
EXISTS 指定一个子查询,测试行是否存在
FREETEXT 根据意思,而不是字面值来搜索数据中的单词
IN WHERE color in (‘red’,‘blue’)
IS NOT NULL
IS NULL 检测NULL值
LIKE 根据通配符进行模式匹配
NOT BETWEEN
NOT IN
NOT LIKE
SOME 比较标量值和单列集中的值。SOME 和 ANY 是等效的

GO

    浏览到文件 instawdb.sql,并单击“打开”。该文件的默认位置为 C:Program FilesMicrosoft SQL Server90ToolsSamplesAdventureWorks OLTP。

这里给出的示例是在WHERE子句中使用相关子查询的一个非常简单的例子。希望通过这样一个简单的示例,它很容易理解普通子查询和相关子查询之间的区别。通常,一个相关的子查询可能要复杂得多。此外,请记住,在不使用相关子查询的情况下,可能还有其他方法来满足您的业务需求。正如您所看到的,编写一个相关子查询非常类似于普通子查询,但是您不能独立地运行相关子查询。

USE AdventureWorks;
GO
SELECT Name, ListPrice, 
(SELECT AVG(ListPrice) FROM Production.Product) AS Average, 
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1

 

9.3:多层嵌套

CustomerID  TotalPurchase         Rebate

     %:0~N个任意字符;

报告1:运行清单1中的代码时返回的结果

13:公共表表达式common_table_expression

VIN varchar(50),

3:between

当经理运行此查询时,她注意到有许多类似的汽车,其发票金额相同,有不同的InvoicePriceRatio值。为了最大限度地提高她的发票价格,她要求她支持编写一个查询,以更新她所有汽车上的StickerPrice,这样每辆车都有相同的CarName值,就有相同的InvoicePriceRatio。她希望IT人员将StickerPrice设置为与CarName的最大价格相同的值。这样,所有具有相同CarName值的汽车将具有相同的StickerPrice值。为了完成CarInventory表的更新,IT人员运行清单6中的transact

 

在编写相关子查询时,需要有___________________。(填入空白)

     结果:

       为了演示如何使用相关子查询,我需要一些测试数据。我的所有示例都将使用AdventureWorks2008R2数据库,而不是创建自己的测试数据。如果您想要跟随并运行我在您的环境中的示例,那么您可以从这里下载AdventureWorks2008R2数据库: 。

      返回的结果为:Day,Evening,Night,

CarName varchar(50),

 

清单6:相关子查询,以更新CarInventory以最大价格

  9.3:多层嵌套

GROUP BY Outer_H.[CustomerID]

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
--where ModifiedDate between '7/28/2002' and '7/29/2002'
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
--HAVING SalesOrderID = 43875
--HAVING ModifiedDate between '7/28/2002' and '7/29/2002' --error
ORDER BY SalesOrderID ;

清单4中的代码创建了一个CarInventory表,然后填充了8行,表示当前库存的汽车。

     可以在许多位置指定子查询(必须全部掌握):

StickerPrice decimal (7,2),

   8.1:使用DISTINCT消除重复值

29736       157700.6034           15770.060340

      结果:(290 行受影响)
select DISTINCT HireDate FROM HumanResources.Employee

29770       151824.9944           15182.499440

4:like

ID int identity,

13:公共表表达式common_table_expression

清单5:InvoicePriceRatio查询

     下面列出了使用 UNION 合并两个查询结果集的基本规则:

  • sql语句,其中包含一个相关子查询。

    UPDATE CarInventory 

    SET StickerPrice = (SELECT MAX(StickerPrice)

                        FROM CarInventory Inner_CI

                        WHERE Inner_CI.CarName = Outer_CI.CarName) 

    FROM CarInventory Outer_CI;

      说明已经将重复的抵消了。


    declare @percentage float

CustomerID

9:子查询

29818       179916.2877           17991.628770

    top允许根据定义的行的数量或者百分比查询出开始的N行。如:

CREATE TABLE CarInventory (

    说明,1:创建了新表Store_Archive,2:有701行数据被复制到了Store_Archive。

在WHERE子句中关联子查询的示例

 

A.是    B.否

select * FROM HumanResources.Employee

摘要

    set @percentage =1

清单4:创建和填充测试表的代码

   8.3使用INTO字句

相关子查询清单3中的代码使用CustomerID从集团外部查询中的条款在相关子查询。相关子查询的执行将为每一行返回GROUP BY子句。这使得HAVING子句计算总金额的产品从外部查询卖给每个顾客ID的值求和,全列每次销售订单头记录在记录与从外部查询相关信息。清单3中的翻译T-SQL语句只返回了顾客ID已经购买超过15万美元的产品的一行。

   将两个或更多查询的结果合并为单个结果集,该结果集包含联合查询中的所有查询的全部行。

SELECT COUNT(*) FROM Sales.SalesOrderDetail

5:escape

包含相关子查询的UPDATE语句的示例

    结果:

a是不正确的,因为随着候选行数量的增加,相关子查询的执行次数增加,而transact

sql语句性能变得更糟。c是不正确的,因为相关子查询必须包含来自外部查询的一个或多个行,而不是内部查询。

问题3

正确的答案是b .如果您尝试独立于完整的transact - sql语句运行相关子查询,那么相关的子查询语句将会失败。

这篇文章是通往t - sql的阶梯的一部分:除了基本的阶梯。

     以下示例使用简单 HAVING 子句从 SalesOrderDetail 表中检索超过 $100000.00 的每个 SalesOrderID 的总计。

清单3:有子句的相关子查询

(1124 行受影响)

SELECT Outer_H.[CustomerID]

      结果:(164 行受影响)

ORDER BY Rebate DESC;

        SELECT字句技术有很多,除了最简单的拼接等,下面介绍个人认为最有用的。

('Explorer','EXP2014123493A',47129.98, 38201.87),                              

 


     指定组或聚合的搜索条件。HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP BY 子句,则 HAVING 的行为与 WHERE 子句一样。

('Pathfinder','NPF2014987365A',32587.73,28917.10),

    指定用来放置输出行的组。如果 SELECT 子句 <select list> 中包含聚合函数,则 GROUP BY 将计算每组的汇总值。

HAVING (SELECT SUM(Inner_H.[SubTotal]) FROM [Sales].[SalesOrderHeader] AS Inner_H

     必须着重说说代替表达式的子查询。在 Transact-SQL 中,除了在 ORDER BY 列表中以外,在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何能够使用表达式的地方都可以用子查询替代。


   

('Pathfinder','NPF2014098587C',35876.12,28917.10),

 

相关子查询与普通子查询类似,而相关子查询可以独立于整个transact - sql语句(True或False)运行。

3:between

30107

Use AdventureWorks;
GO
SELECT LastName, FirstName
FROM Person.Contact
WHERE ContactID IN
    (SELECT ContactID
     FROM HumanResources.Employee
     WHERE EmployeeID IN
        (SELECT SalesPersonID
         FROM Sales.SalesPerson)

 

('Grand Cherokee','JGC20141234345X',41678.45,36201.86),

11.2:UNION

关联子查询是一个内部查询,它包含来自外部查询的一个或多个列。关联子查询对外部查询的每个候选行执行一次。因为关联子查询包含来自外部查询的列,因此它不能独立于外部查询运行。相关子查询有它们的位置,尽管在外部查询中识别出大量候选行时,从性能角度看,它们的伸缩性并不好。

     [^]:指定不再范围中的任何单个字符;

如果您回顾清单1中的代码,您将看到我使用相关子查询限制了我的位置。子查询是圆括号中的代码,我从清单1中提取了相关的子查询代码,并将其放入清单2中。

10:比较使用 EXISTS 和 IN 的查询

       在这个第二阶梯中,我们了解到正常的子查询只是在另一个翻译T-SQL语句内的一个SELECT语句,在这个语句中子查询如果独立于外部查询而返回结果。关联子查询是子查询的一种形式,它不能独立于外部查询运行,因为它包含来自外部查询的一个或多个列。相关子查询,就像普通的子查询,有时被称为内部查询。如果相关子查询(内部查询)独立于外部查询运行,则它将返回一个错误。因为内部查询的执行依赖于来自外部查询的值,因此它被称为相关子查询。

 

('Pathfinder','NPF2014239657B',33577.54,28917.10),

    (2 行受影响)

什么是相关子查询?

2:基本运算符和表达式

29940       175358.3954           17535.839540

9:子查询

29995       156984.5148           15698.451480

    运行脚本之前,在脚本中找到语句 SET @data_path = @sql_path + 'AWDB';,并更改该语句使其指向 instawdb.sql 脚本的位置。例如,SET @data_path = 'C:Program FilesMicrosoft SQL Server90ToolsSamplesAdventureWorks OLTP';

相关子查询示例的示例数据

10:比较使用 EXISTS 和 IN 的查询

清单2:清单1中的子查询代码

select OrderDate, sum(totalDue) TotalDueByOrderDate from Sales.SalesOrderHeader
where ShipDate between '7/28/2002' and '7/29/2002'
--group by OrderDate
group by all OrderDate

29722

     衍生表是指在FROM字句中作为表的SELECT语句。

销售经理周期性地使用清单5中的查询来查看他的InvoicePriceRatio。

    一种典型的用法是复制数据到新表(这个新表可以被创建为永久表、临时表或全局临时表),如下代码:

Msg 4104, Level 16, State 1, Line 3

   11.2:UNION

29987       172169.4612           17216.946120

6:TOP

7.1:GROUP BY ALL

    select top (@percentage)  percent * from Sales.SalesOrderHeader

 

   8.2:返回拼接的结果

Warning: Null value is eliminated by an aggregate or other SET operation.

select OrderDate, sum(totalDue) TotalDueByOrderDate from Sales.SalesOrderHeader
where ShipDate between '7/28/2002' and '7/29/2002'
group by OrderDate

12:TABLESAMPLE

2:基本运算符和表达式

     这说明:ALL包含所有组和结果集,甚至包含那些其中任何行都不满足 WHERE 子句指定的搜索条件的组和结果集。如果指定了 ALL,将对组中不满足搜索条件的汇总列返回空值。

where'7/28/2002' < ShipDate and ShipDate < '7/29/2002'

    也许有人会用,使用:

    使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于,联接使您可以在结果中显示多个表中的列。例如,如果要在结果中包括产品子类别的名称,则必须使用联接版本。

11:联接

  11.1:使用衍生表

      这对于我们处理简单的查询并提高效率有很大的好处。

     []:指定范围或列表中的任何单个字符;

    当然,如果你仅仅想创建新表,而不想复制任何数据,有一个简洁的方法是:

8:SELECT字句技术

    子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
 FROM HumanResources.Employee AS b
 WHERE a.ContactId = b.ContactID
 AND a.LastName = 'Johnson');
GO

    下面的查询使用 IN

本章摘要

    上面这句话不太好理解,更好的理解应该解释为:

9.2:代替表达式的查询

1:安装AdventureWorks

 

    以下示例比较了两个语义等同的查询。第一个查询使用 EXISTS,第二个查询使用 IN

     在上面的代码中,加入ALL,即:

11.1:使用衍生表

     指定临时命名的结果集,这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询,并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中,作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

    执行脚本。

8.2:返回拼接的结果

   9.2:代替表达式的查询

     _:1个字符;

    “由于在SELECT字句中使用了聚合函数,未聚合的列必须出现在GROUP BY子句中。”

select SalesOrderID, ShipDate from Sales.SalesOrderHeader
where ShipDate between '7/28/2002' and '7/29/2002'

 

编辑:数据库 本文来源:SELECT字句技术澳门新濠3559,关联子查询是子查询

关键词: