将行与关联的子查询进行比较

12 分钟

相关查询是引用外部查询中的列的子查询，创建一个依赖项，使子查询对外部查询处理的每一行执行一次。虽然这听起来效率低下，但相关查询在逐行比较和计算中非常有用，而这些计算很难或不可能通过其他方式表达。

了解关联的子查询执行

关联的子查询引用外部查询中的一个或多个列，从而在两者之间创建逻辑依赖关系。与执行一次并返回固定结果的常规子查询不同，相关子查询重复执行，对外部查询处理的每一行执行一次。

将其视为嵌套循环：对于外部查询中的每一行，数据库使用该行的值计算子查询。此行为可实现功能强大的逐行比较，但也意味着你需要了解执行模型来编写高效的查询。

请考虑这两个查询有何不同：

-- Non-correlated subquery (executes once)
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM SalesLT.Product);

-- Correlated subquery (executes per outer row)
SELECT p1.ProductID, p1.Name, p1.ListPrice
FROM SalesLT.Product AS p1
WHERE p1.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p1.ProductCategoryID  -- References outer query
);

在非相关示例中，子查询在所有产品中计算单个平均价格。此值计算一次，然后将每个产品的价格与该固定数字进行比较。

在相关示例中，子查询从外部查询中引用了p1.ProductCategoryID。这会创建依赖项：对于每个产品行，子查询将计算该特定类别的平均价格。 “自行车”类别中的产品与自行车平均值进行比较，而“附件”中的产品则与配件平均值进行比较。

注释

查询优化器通常会在内部将相关子查询转换为等效联接。但是，即使物理执行不同，了解逻辑关联行为也有助于编写正确的查询。

使用关联的子查询进行筛选

WHERE 子句中的相关子查询支持行特定的筛选条件，这是静态比较无法实现的。相较于与单个固定值进行比较，每一行是根据该行属性计算出的动态值进行评估的。

当需要识别组中的离群值、查找超出其类别阈值的记录或应用因上下文而异的业务规则时，此模式非常有用。以下示例查找价格高于其类别平均值的产品，这意味着低价配件可能标记为昂贵，而价格较高的自行车可能不是：

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS Category
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
)
ORDER BY pc.Name, p.ListPrice DESC;

可以应用相同的模式来识别其行为不同于其个人基线的客户。

以下查询查找至少放置了一个超过其平均订单值的订单的客户，这有助于识别异常购买模式或高价值交易：

SELECT DISTINCT
    c.CustomerID,
    c.FirstName,
    c.LastName
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
WHERE soh.TotalDue > (
    SELECT AVG(soh2.TotalDue)
    FROM SalesLT.SalesOrderHeader AS soh2
    WHERE soh2.CustomerID = c.CustomerID
);

`EXISTS`与关联的子查询一起使用

运算符 EXISTS 与关联的子查询结合使用，测试相关表中是否存在任何匹配的行，并返回简单的 true 或 false 结果。此模式非常高效，因为数据库引擎可以在找到第一个匹配行后立即停止搜索。与返回实际数据的子查询不同， EXISTS 只需确认是否存在或缺席。

EXISTS当需要回答“哪些客户已下订单？”或“哪些产品从未销售？”等问题时使用？子查询通常使用SELECT 1，因为实际值并不重要：

-- Find customers who have placed at least one order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

-- Find customers who have never placed an order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

EXISTS 当你需要检查合并多个条件的复杂条件时，会变得更加有价值。可以在子查询中添加任何筛选逻辑，外部查询仅包含至少存在一个匹配相关行的行。

以下示例演示如何查找具有高数量订单和类别的产品，其中每个产品都满足价格阈值：

-- Find products that have been ordered in quantities greater than 10
SELECT p.ProductID, p.Name
FROM SalesLT.Product AS p
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderDetail AS sod
    WHERE sod.ProductID = p.ProductID
      AND sod.OrderQty > 10
);

-- Find categories where all products are priced above $100
SELECT pc.ProductCategoryID, pc.Name
FROM SalesLT.ProductCategory AS pc
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.Product AS p
    WHERE p.ProductCategoryID = pc.ProductCategoryID
      AND p.ListPrice <= 100
);

小窍门

EXISTS 在使用子查询时通常优于 IN，尤其是在检查大型表中是否存在时。优化器可以在找到第一个匹配 EXISTS项后停止，而 IN 可能需要检索所有匹配值。

在`SELECT`中使用相关子查询来计算值

SELECT 子句中的相关子查询为结果集中的每一行计算一个单独的值。通过此模式，可以将相关表中的聚合值或派生值与主行的详细信息一起包含在一起，而不会将结果折叠到组中。

如果需要显示上下文信息（例如，将每个产品与其类别的平均价格一起显示），或者每个员工与其部门的总人数一起显示，此方法非常有用。子查询每行执行一次，使用该行的值筛选计算：

-- Show each product with its category's average price
SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS CategoryAvgPrice,
    p.ListPrice - (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS DifferenceFromAvg
FROM SalesLT.Product AS p;

还可以使用此模式对相关记录进行计数或从相关表中检索特定值。以下查询生成一个客户摘要，其中包含每个客户的订单计数和最新的订单日期，为每个客户行单独计算：

-- Show each customer with their order count
SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName,
    (
        SELECT COUNT(*)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS OrderCount,
    (
        SELECT MAX(soh.OrderDate)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS LastOrderDate
FROM SalesLT.Customer AS c;

注释

子句中的 SELECT 关联子查询必须只返回一个值。如果子查询可以返回多个行，请将其包装在聚合函数中，例如 MAX()， MIN()或 SUM()。

使用关联的子查询查找每个组的前 N 个条目

相关子查询最实用的应用程序之一是在每个组中查找前 N 个项。此模式回答了“每个类别中三种最昂贵的产品是什么？”或“每个区域中前五名销售人员是谁？”

相关子查询会检查每一行，通过确定同组中有多少其他行排名更高，从而判断该行是否在其组的前N名之内。当窗口函数不可用或需要窗口函数无法表达的复杂排名逻辑时，此方法非常有效。

以下查询通过选择其 ID 在所属类别中排名前三的产品，查找每个类别的三款最昂贵产品：

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID IN (
    SELECT TOP 3 p2.ProductID
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
    ORDER BY p2.ListPrice DESC
)
ORDER BY pc.Name, p.ListPrice DESC;

另一种方法是统计高于当前行的项数。如果少于 N 个项目具有更高的值，则当前行属于前 N 名。此技术处理并列情况的方式不同，当需要所有并列第 N 名的项目时非常有用：

-- Find products that are in the top 3 by price within their category
SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE (
    SELECT COUNT(*)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
      AND p2.ListPrice > p.ListPrice
) < 3
ORDER BY pc.Name, p.ListPrice DESC;

比较连续行

相关子查询可以根据排序条件访问上一行或后续行中的值，从而启用周期内比较和趋势分析。此模式可用于计算连续记录之间的更改，例如将每个订单与上一个顺序进行比较或跟踪值随时间变化的方式。

子查询通过筛选逻辑序列中当前行之前（或之后）的行来查找相关行，然后对结果进行排序以获取紧邻行：

-- Show each order with the previous order's total
SELECT 
    soh.SalesOrderID,
    soh.OrderDate,
    soh.TotalDue,
    (
        SELECT TOP 1 soh2.TotalDue
        FROM SalesLT.SalesOrderHeader AS soh2
        WHERE soh2.CustomerID = soh.CustomerID
          AND soh2.OrderDate < soh.OrderDate
        ORDER BY soh2.OrderDate DESC
    ) AS PreviousOrderTotal
FROM SalesLT.SalesOrderHeader AS soh
ORDER BY soh.CustomerID, soh.OrderDate;

小窍门

对于连续行比较，窗口函数通常LAG()LEAD()比相关子查询更高效且可读。如果需要比窗口函数支持更复杂的条件，请使用相关子查询。

在相关子查询和替代项之间进行选择

相关子查询并不总是最佳方法。下表可帮助你选择正确的技术：

使用此方法	当您需要...
相关子查询	将每行与基于该行属性的动态计算值进行比较，测试是否存在， `EXISTS`/`NOT EXISTS`或者使用复杂选择逻辑检索每行完全相同的相关值。
联接	从多个表中检索列，或者在关系简单并且不需要逐行计算时直接获取数据。
窗口函数	使用 `LAG()`/`LEAD()` 计算累计总计、排名或访问前一行/后一行。对于这些模式，比相关子查询更高效。
CTE	多次引用相同的计算结果，或将复杂逻辑分解为命名的可读步骤。

性能注意事项

未正确优化时，相关子查询可能会影响性能。由于子查询针对外部查询中的每个行执行一次，因此设计不佳的关联查询可能会导致对大型表执行数千或数百万个子查询。

遵循以下准则优化相关子查询性能：

在相关列上创建索引：确保在子查询的 WHERE 子句中被引用的列上创建索引，以连回到外部查询。例如，如果子查询在ProductCategoryID列上进行筛选，该列上的索引允许数据库快速查找匹配的行，而不是对于每一个外部行都扫描整个表。
在索引中包含其他列：如果子查询还筛选或聚合其他列，请考虑使用复合索引。基于 (ProductCategoryID, ListPrice) 索引的索引支持单个索引查找中的关联查找和基于价格的筛选或聚合。
评估替代方法：许多相关的子查询可以重写为联接或窗口函数，性能更佳。如果您需要查找每个组的最大值，窗口函数 ROW_NUMBER() 通常优于为每行选择 MAX() 的相关子查询。
查看执行计划：使用 SET STATISTICS IO ON 并检查实际执行计划，以了解优化器如何处理相关子查询。优化器可以在内部将其转换为联接，也可以按写入方式逐行执行该联接。
使用实际数据量进行测试：在小型测试数据集上表现良好的相关子查询，在生产环境中的大型表上可能会变慢。在部署到生产环境之前，始终使用具有代表性的数据进行基准测试。

重要

在大型表上使用相关子查询时，请始终查看执行计划。优化器可以有效地转换它们，但复杂的相关性可能会受益于查询重写。

有关子查询的详细信息，请参阅子查询（Transact-SQL）和 EXISTS（Transact-SQL）。

反馈

此页面是否有帮助？