SQL中如何使用子查询_SQL子查询的写法与应用

SQL子查询通过将一个SELECT语句嵌入另一个查询中实现复杂数据操作，可在WHERE、SELECT、FROM、HAVING子句中使用，核心应用场景包括条件筛选、标量值计算、派生表构建及聚合过滤，需根据性能与可读性权衡选择子查询或JOIN，并注意优化关联子查询、IN子句及索引设计。

子查询是将一个SELECT语句嵌入另一个查询中，作为主查询的一部分执行。其核心作用包括：

• 条件筛选：通过子查询返回的结果集过滤主查询数据（如WHERE CustomerID IN (...)）。
• 标量值计算：在SELECT子句中返回单一值（如计算差值、比例）。
• 派生表构建：在FROM子句中生成临时表供主查询使用。
• 聚合过滤：在HAVING子句中对聚合结果进一步筛选。

示例：筛选美国客户的订单

• IN/NOT IN：匹配子查询返回的列表。-- 筛选无对应客户的订单（孤儿订单）SELECT OrderID, CustomerID FROM Orders WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers);
• EXISTS/NOT EXISTS：检查子查询是否存在匹配行（效率通常优于IN）。-- 筛选至少有一笔订单的客户SELECT CustomerID, Name FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

• 计算与聚合值的差值、比例等。-- 计算产品价格与平均价格的差值SELECT ProductName, Price, Price - (SELECT AVG(Price) FROM Products) AS PriceDifference FROM Products;

• 将子查询结果作为临时表供主查询使用。-- 计算每个类别的平均价格，并筛选高于平均价的产品SELECT p.ProductName, p.Price, c.AvgPriceFROM Products pJOIN ( SELECT CategoryID, AVG(Price) AS AvgPrice FROM Products GROUP BY CategoryID) c ON p.CategoryID = c.CategoryIDWHERE p.Price > c.AvgPrice;

• 对分组后的结果进一步筛选。-- 筛选订单数超过10的客户SELECT CustomerID, COUNT(*) AS OrderCountFROM OrdersGROUP BY CustomerIDHAVING COUNT(*) > (SELECT AVG(OrderCount) FROM ( SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID) AS t);

• 标量值需求：如计算差值、比例时，标量子查询更直观。
• 存在性检查：EXISTS在只需判断是否存在匹配时效率更高（找到即停止）。
• 逻辑清晰性：当业务逻辑复杂时，子查询可能比JOIN更易理解。

• 多表字段合并：需合并多个表的列时，JOIN逻辑更清晰。
• 大数据量性能：数据库优化器通常能更好优化JOIN，尤其在有索引时。-- 显示订单详情（客户名称+产品名称）SELECT o.OrderID, c.Name AS CustomerName, p.ProductNameFROM Orders oJOIN Customers c ON o.CustomerID = c.CustomerIDJOIN OrderDetails od ON o.OrderID = od.OrderIDJOIN Products p ON od.ProductID = p.ProductID;

• 问题：内层查询依赖外层每一行，导致重复执行（如计算每个类别的平均价）。
• 优化方案：

  改用JOIN：通过预计算聚合值减少重复计算。-- 优化前（关联子查询）SELECT ProductName, Price, CategoryIDFROM Products p1WHERE Price > ( SELECT AVG(Price) FROM Products p2 WHERE p2.CategoryID = p1.CategoryID);-- 优化后（JOIN）SELECT p1.ProductName, p1.Price, p1.CategoryIDFROM Products p1JOIN ( SELECT CategoryID, AVG(Price) AS AvgPrice FROM Products GROUP BY CategoryID) c ON p1.CategoryID = c.CategoryIDWHERE p1.Price > c.AvgPrice;

  使用窗口函数（如支持）：SELECT ProductName, Price, CategoryIDFROM ( SELECT ProductName, Price, CategoryID, AVG(Price) OVER (PARTITION BY CategoryID) AS AvgPrice FROM Products) tWHERE Price > AvgPrice;

• 问题：IN子句中子查询返回大量数据时性能下降。
• 优化方案：

  改用EXISTS：若只需检查存在性。

  改用JOIN：如筛选孤儿订单时，LEFT JOIN + IS NULL更高效。-- 优化前（NOT IN）SELECT OrderID, CustomerID FROM Orders WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers);-- 优化后（LEFT JOIN）SELECT o.OrderID, o.CustomerID FROM Orders oLEFT JOIN Customers c ON o.CustomerID = c.CustomerIDWHERE c.CustomerID IS NULL;

• 确保子查询中涉及的关联列（如CustomerID、CategoryID）和筛选列有索引。

• 示例：筛选每个类别销售额前3的产品。-- 方法1：关联子查询（数据量小时适用）SELECT CategoryID, ProductName, SalesFROM Products p1WHERE ( SELECT COUNT(DISTINCT p2.Sales) FROM Products p2 WHERE p2.CategoryID = p1.CategoryID AND p2.Sales > p1.Sales) < 3ORDER BY CategoryID, Sales DESC;-- 方法2：窗口函数（推荐）SELECT CategoryID, ProductName, SalesFROM ( SELECT CategoryID, ProductName, Sales, RANK() OVER (PARTITION BY CategoryID ORDER BY Sales DESC) AS rnk FROM Products) tWHERE rnk <= 3;

• 示例：计算每日累计销售额。-- 方法1：关联子查询（数据量小时适用）SELECT SaleDate, DailySales, (SELECT SUM(DailySales) FROM DailySalesTable WHERE SaleDate <= d.SaleDate) AS CumulativeSalesFROM DailySalesTable dORDER BY SaleDate;-- 方法2：窗口函数（推荐）SELECT SaleDate, DailySales, SUM(DailySales) OVER (ORDER BY SaleDate) AS CumulativeSalesFROM DailySalesTableORDER BY SaleDate;

• 示例：筛选重复客户ID。SELECT CustomerID, CustomerNameFROM CustomersWHERE CustomerID IN ( SELECT CustomerID FROM Customers GROUP BY CustomerID HAVING COUNT(*) > 1);

• 灵活使用：子查询是SQL的强大工具，但需结合场景选择（如标量值用子查询，多表合并用JOIN）。
• 性能优先：避免关联子查询和大量数据的IN子查询，优先