SQL 分组查询中的非聚合列要求及实例解析

在 SQL 查询中，当我们对数据进行分组时，通常会用到 GROUP BY 子句。SQL 标准要求：所有非聚合列（即没有使用聚合函数的列）都必须出现在 GROUP BY 子句中，或者是聚合函数的结果。这篇文章通过一个实例来说明这个规则的原因及如何正确书写查询语句。

场景描述

我们有三张表：Customers（顾客信息）、Orders（订单信息）、OrderItems（订单项信息），它们分别包含以下字段：

• Customers 表

  • cust_id：顾客 ID
  • cust_name：顾客名称

• Orders 表

  • order_num：订单号
  • cust_id：顾客 ID（与 Customers 表关联）

• OrderItems 表

  • order_num：订单号（与 Orders 表关联）
  • quantity：商品数量
  • item_price：商品单价

需求是返回每个顾客的 cust_name 和与之关联的订单号 order_num，以及每个订单的总价 OrderTotal，并按顾客名称和订单号进行排序。

数据示例

Customers 表：

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

Orders 表：

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

OrderItems 表：

order_num	quantity	item_price
a1	1000	10
a2	200	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

正确的 SQL 查询

为了实现需求，我们需要将三张表进行连接，并计算每个订单的总价。计算总价的方法是将 quantity 和 item_price 相乘，并对每个订单的所有项进行求和。最终的 SQL 查询如下：

SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c
JOIN Orders o ON c.cust_id = o.cust_id
JOIN OrderItems oi ON o.order_num = oi.order_num
GROUP BY c.cust_name, o.order_num
ORDER BY c.cust_name, o.order_num;

查询解析

• 连接三张表：通过 cust_id 将 Customers 和 Orders 表连接，通过 order_num 将 Orders 和 OrderItems 表连接，获取顾客、订单和订单项的完整信息。
• 计算订单总价：使用 SUM(quantity * item_price) 计算每个订单的总金额。
• 分组与排序：GROUP BY c.cust_name, o.order_num 按顾客名称和订单号分组，使每个分组对应一个顾客的一个订单，从而计算出每个订单的总金额。ORDER BY c.cust_name, o.order_num 对结果按顾客名称和订单号排序。

查询结果

查询将返回顾客的姓名、订单号以及该订单的总价，并按顾客名称和订单号进行排序：

cust_name	order_num	OrderTotal
andy	a1	10000
ben	a2	2000
tony	a3	150
tom	a4	1250
an	a5	375
hex	a7	49

错误示例及解析

一个常见的错误查询示例如下：

SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c, Orders o, OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name
ORDER BY c.cust_name, o.order_num;

错误原因：

• 这个查询中只在 GROUP BY 子句中使用了 cust_name，但 order_num 未包含在 GROUP BY 中，导致 SQL 无法确定如何处理 order_num。
• 在 SQL 中，GROUP BY 子句中的列需要包含所有非聚合列（即未被聚合函数包裹的列）。否则，SQL 将无法知道如何处理这些非聚合列的值，从而导致错误。

1. 什么是聚合列和非聚合列？

• 聚合列：指的是在 SELECT 语句中通过聚合函数（如 SUM()、AVG()、MAX()、MIN()、COUNT() 等）计算出的列。它们通常是针对分组数据进行的汇总统计，例如求某个分组中的所有值的和、平均值等。

• 非聚合列：在 SELECT 语句中未使用聚合函数的列，即直接显示的列，没有进行任何聚合操作。这些列通常用于显示某个分组的特定属性。

2. 为什么非聚合列必须出现在 GROUP BY 子句中，或者是聚合函数的结果？

在分组查询中，SQL 标准要求所有出现在 SELECT 语句中的列要么是聚合函数的结果，要么出现在 GROUP BY 子句中。其原因如下：

• 确定性原则：在分组查询中，每个分组内可能包含多行数据，如果我们在 SELECT 中选择了一个非聚合列，但没有将其包含在 GROUP BY 子句中，SQL 不知道应该选取哪个具体的值，因为每个分组中的该列值可能不同，这会导致不确定性。

• 保证数据一致性：当一个查询返回结果时，用户会希望每一行数据都是确定的、清晰的。如果没有将非聚合列放入 GROUP BY，则会出现难以理解的数据，可能导致数据含义不清或误导。

举例说明

假设有一个 sales 表，结构如下：

id	region	sales_amount
1	East	200
2	East	150
3	West	300
4	West	250

我们希望按 region 进行分组，求每个地区的总销售额。正确的 SQL 查询应该是：

SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;

输出结果：

region	total_sales
East	350
West	550

在这个例子中，SUM(sales_amount) 是一个聚合列，而 region 是一个非聚合列。因为 region 出现在 GROUP BY 子句中，SQL 知道需要按 region 分组，然后对每组的数据进行汇总。

错误的写法

如果我们写成如下形式，将 id（非聚合列）放在 SELECT 语句中，却不出现在 GROUP BY 中：

SELECT id, region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;

这样 SQL 会报错，因为每个 region 可能包含多行不同的 id，系统无法确定返回哪个 id。这就是 SQL 标准所要避免的“不确定性”问题。

如何解决？

如果确实需要某个非聚合列出现在查询结果中，那么我们有两种选择：

1. 将非聚合列加入 GROUP BY 子句中：如果 id 是我们希望按某个粒度分组的属性，可以将其加入 GROUP BY。

   SELECT id, region, SUM(sales_amount) AS total_sales
   FROM sales
   GROUP BY id, region;
   

2. 使用聚合函数处理该列：如果想得到某个分组内的特定 id，可以使用聚合函数，如 MAX(id) 或 MIN(id)，以明确返回的值。

   SELECT MAX(id) AS example_id, region, SUM(sales_amount) AS total_sales
   FROM sales
   GROUP BY region;
   

总结

在 SQL 中，当我们进行分组查询时，所有出现在 SELECT 语句中的非聚合列都必须出现在 GROUP BY 子句中，或者使用聚合函数包裹。这样做的原因是，GROUP BY 子句能确保查询结果是确定的、清晰的。否则，SQL 将无法理解如何处理这些非聚合列，可能导致错误。