答案：聚合函数在子查询中用于解决需先计算汇总值再进行比较或过滤的复杂查询问题，如个体与群体比较、基于分组结果筛选等。通过非关联子查询实现一次性聚合（如高于平均价格的产品），或通过关联子查询实现行级动态计算（如部门内高于平均薪的员工）。非关联子查询性能更优，通常只执行一次；关联子查询每行执行一次，易导致性能瓶颈，建议用JOIN派生表或窗口函数替代。多层嵌套可读性差，推荐使用WITH子句分解逻辑，提升可维护性。窗口函数常为更优替代方案。
SQL聚合函数在子查询中的应用，核心在于它提供了一种灵活的方式，让我们可以在一个查询内部，基于另一组数据的聚合结果进行过滤、计算或比较。这就像你在处理一份复杂的报告时，需要先算出某个部门的平均销售额，然后再找出那些销售额高于这个平均值的员工，或者将这个平均值作为某个计算的基准。它不是一个单一的技巧，而是一系列策略，用于处理那些单靠
WHERE
或
GROUP BY
无法直接解决的复杂数据关系。
解决方案
在SQL中，聚合函数（如
COUNT()
,
SUM()
,
AVG()
,
MAX()
,
MIN()
）通常与
GROUP BY
子句一起使用，对分组后的数据进行汇总。当它们出现在子查询中时，情况会变得更有趣，也更强大。我们通常会遇到两种主要场景：非关联子查询和关联子查询。
非关联子查询中的聚合函数
这种子查询可以独立执行，它不依赖于外部查询的任何值。它会返回一个单一的值（标量子查询）或一组值，然后外部查询使用这些结果。
示例1：找出价格高于平均价格的产品
SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products);
这里，
(SELECT AVG(price) FROM products)
是一个非关联子查询。它首先计算出所有产品的平均价格，然后外部查询用这个平均价格来筛选产品。这是一种非常常见的用法，解决了很多“高于平均值”或“低于最小值”这类问题。
示例2：找出每个类别中价格最高的产品
这需要一点技巧，因为
MAX(price)
本身是针对整个表，或者在
GROUP BY
后针对每个组。如果想在子查询中获取每个类别的最大值，然后与外部查询的每个产品进行比较，通常我们会将聚合结果作为一个派生表（Derived Table）来处理。
SELECT p.product_name, p.category, p.price FROM products p INNER JOIN (SELECT category, MAX(price) AS max_category_price FROM products GROUP BY category) AS sub ON p.category = sub.category AND p.price = sub.max_category_price;
在这个例子里，子查询
(SELECT category, MAX(price) AS max_category_price FROM products GROUP BY category)
计算了每个类别的最高价格。这个结果集被当作一个临时表
sub
，然后我们通过
INNER JOIN
将其与原始
products
表连接起来，找出那些价格恰好等于其类别最高价格的产品。我个人觉得这种方式比复杂的关联子查询在很多情况下更清晰，也更容易被优化器处理。
关联子查询中的聚合函数
关联子查询的执行依赖于外部查询的每一行。它会为外部查询的每一行重新执行一次。这使得它能够进行更细粒度的行级比较。
示例：找出每个部门中薪水高于该部门平均薪水的员工
SELECT e.employee_name, e.department_id, e.salary FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
这里的
(SELECT AVG(salary) FROM employees WHERE department_id = e.department_id)
就是一个关联子查询。
e.department_id
来自外部查询的当前行。对于外部查询的每一名员工，子查询都会计算其所在部门的平均薪水，然后将该员工的薪水与这个平均值进行比较。这种模式虽然强大，但在处理大量数据时，性能可能会成为一个需要关注的问题，因为它会重复执行多次。
为什么需要在子查询中使用聚合函数？它解决哪些常见的SQL查询难题？
有时候，我们想从数据中提取的信息，并不是简单地筛选行或汇总整个表就能得到的。聚合函数在子查询中的运用，正是在解决这些“需要先看全局或局部汇总，再做决策”的复杂问题。
一个很常见的场景就是“比较个体与群体”。比如，你想找出那些销售额超过公司平均水平的销售员。你不能直接在
WHERE
子句里写
WHERE sales > AVG(sales)
，因为
AVG()
是聚合函数，不能直接用在
WHERE
里。这时候，子查询就派上用场了，先用子查询计算出
AVG(sales)
，然后外部查询再去比较。
再比如，“基于分组聚合结果进行筛选”。我遇到过这样的需求，要找出那些所有订单总金额都低于某个阈值的客户。这和
HAVING
子句有点像，但如果这个阈值本身也需要通过另一个聚合计算得出，或者你需要将这个结果与其他表的数据关联，子查询的灵活性就体现出来了。它允许你先对一部分数据进行聚合，然后将这个聚合结果作为一个独立的“事实”来使用，无论是用于
WHERE
过滤，还是用于
JOIN
连接，甚至是作为
SELECT
列表中的一个计算列。
它也帮助我们处理一些“排名”或“百分比”的场景。虽然现在有了窗口函数，很多排名问题变得更简单，但在没有窗口函数或者某些特定需求下，子查询依然是重要的工具。比如，计算某个产品在所有产品中的价格百分比，你可能需要先算出
MAX(price)
或
SUM(price)
，再进行计算。子查询的这种能力，让SQL能够处理更细致、更具洞察力的数据分析任务，而不仅仅是基础的数据检索。
关联子查询与非关联子查询中聚合函数的性能差异与选择考量
这确实是一个非常重要的点，因为性能问题在实际工作中很常见。简单来说，非关联子查询通常比关联子查询的性能要好。
非关联子查询，因为它不依赖于外部查询，所以它只需要执行一次。数据库系统会先完整地计算出子查询的结果，然后将这个结果缓存起来，供外部查询使用。这就像你先准备好一个参考值，然后用这个参考值去对照所有数据。优化器通常能很好地处理这种模式，甚至可能将其转换为
JOIN
操作，进一步提升效率。
关联子查询则不同。它对外部查询的每一行都会重新执行一次。如果外部查询有1000行数据，那么关联子查询理论上就要执行1000次。这在数据量较小的时候可能不明显，但一旦数据量增大，性能问题就会变得非常突出，查询时间会急剧增加。我见过不少新手在不了解其执行机制的情况下，大量使用关联子查询，导致查询耗时数分钟甚至数小时。
选择考量：
-
优先考虑非关联子查询或
JOIN
派生表。 如果你的需求可以通过非关联子查询解决，或者可以通过将聚合结果放在
FROM
子句中形成一个派生表（Derived Table）然后进行
JOIN
来解决，那么通常应该优先选择这些方式。例如，前面“找出每个类别中价格最高的产品”的例子，使用
INNER JOIN
派生表的方式就比使用关联子查询来查找每个类别的最大值要高效得多。
-
当逻辑上必须依赖外部查询的每一行时，才考虑关联子查询。 关联子查询在逻辑上非常直观，特别是当需要进行“行级比较”时，比如“每个部门内，找出高于本部门平均薪水的员工”。这种场景下，如果用
JOIN
派生表来模拟，可能会稍微复杂一些，或者需要使用窗口函数。
-
性能调优。 如果你不得不使用关联子查询，一定要关注它的性能。检查
EXPLAIN
或
ANALYZE
计划，看看子查询是否真的执行了太多次。在某些数据库中，优化器可能会将一些关联子查询优化成更高效的
JOIN
或其他操作，但这并非总是如此。有时候，将关联子查询重写为
EXISTS
子句，或者使用窗口函数（如
AVG() OVER (PARTITION BY ...)
）来替代，能够带来显著的性能提升。例如，上面“每个部门中薪水高于该部门平均薪水的员工”的例子，用窗口函数会是这样：
SELECT employee_name, department_id, salary FROM (SELECT employee_name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary FROM employees) AS sub WHERE salary > avg_dept_salary;
这在我看来，通常是更优的选择，因为它避免了重复计算，而且逻辑上也更清晰。
聚合函数在子查询中的高级应用：如何处理多层嵌套与复杂业务逻辑？
当业务逻辑变得复杂时，单层子查询可能就不够用了，我们可能会遇到多层嵌套的子查询，或者需要结合
WITH
子句（CTE，Common Table Expression）来管理复杂性。
多层嵌套子查询
这就像俄罗斯套娃一样，一个子查询里面又包含另一个子查询。这种结构虽然能解决问题，但可读性和维护性会迅速下降。
示例：找出那些销售额高于其所在区域平均销售额的客户，且该区域的平均销售额又高于全国平均销售额。
SELECT c.customer_name, c.region, c.sales FROM customers c WHERE c.sales > (SELECT AVG(c2.sales) FROM customers c2 WHERE c2.region = c.region) -- 客户销售额高于区域平均 AND (SELECT AVG(c3.sales) FROM customers c3 WHERE c3.region = c.region) > (SELECT AVG(c4.sales) FROM customers c4); -- 区域平均销售额高于全国平均
这个例子虽然有点极端，但展示了多层嵌套的可能性。这里
(SELECT AVG(c2.sales) FROM customers c2 WHERE c2.region = c.region)
是一个关联子查询，而
(SELECT AVG(c4.sales) FROM customers c4)
是一个非关联子查询，它们又嵌套在外部查询的
WHERE
子句中。这种写法，坦白说，如果不是为了演示，我个人会尽量避免，因为它太容易出错，而且性能也可能不好。
使用
WITH
子句 (CTE) 优化复杂逻辑
WITH
子句是处理复杂SQL查询的利器，它允许你定义临时的、命名的结果集，然后可以在主查询或其他
WITH
子句中引用这些结果集。这大大提高了查询的可读性和可维护性，同时在某些情况下也可能带来性能上的优化。
示例：重写上面的复杂查询，使用
WITH
子句。
WITH RegionAvgSales AS ( SELECT region, AVG(sales) AS avg_region_sales FROM customers GROUP BY region ), NationalAvgSales AS ( SELECT AVG(sales) AS avg_national_sales FROM customers ) SELECT c.customer_name, c.region, c.sales FROM customers c INNER JOIN RegionAvgSales ras ON c.region = ras.region CROSS JOIN NationalAvgSales nas WHERE c.sales > ras.avg_region_sales AND ras.avg_region_sales > nas.avg_national_sales;
你看，通过
WITH
子句，我们把计算区域平均销售额和全国平均销售额的逻辑清晰地分离出来，各自命名。然后，在主查询中，我们通过
JOIN
将这些预先计算好的结果集连接起来，进行最终的筛选。这种方式不仅逻辑更清晰，也更容易调试。当业务逻辑需要多步聚合或中间结果时，CTE 几乎是我首选的解决方案。它把一个大问题拆解成几个小问题，分步解决，这很符合人类的思维习惯。
此外，窗口函数也是处理这类复杂聚合逻辑的强大工具。很多时候，原本需要关联子查询或多层嵌套子查询才能解决的问题，通过窗口函数一行代码就能搞定，而且性能通常更好。比如计算每个部门的平均薪水并与个人薪水比较，使用
AVG() OVER (PARTITION BY department_id)
就能在一个
SELECT
语句中完成，避免了子查询的开销。虽然这不直接是“子查询中的聚合函数”，但它是一个非常重要的替代方案，值得在考虑复杂聚合时一并考虑。