深圳建网站价格,负责网站开发的岗位,株洲公司做网站,学习网页设计中遇到的心得体会在 SQL 查询中#xff0c;当我们对数据进行分组时#xff0c;通常会用到 GROUP BY 子句。SQL 标准要求#xff1a;所有非聚合列#xff08;即没有使用聚合函数的列#xff09;都必须出现在 GROUP BY 子句中#xff0c;或者是聚合函数的结果。这篇文章通过一个实例来说明这…在 SQL 查询中当我们对数据进行分组时通常会用到 GROUP BY 子句。SQL 标准要求所有非聚合列即没有使用聚合函数的列都必须出现在 GROUP BY 子句中或者是聚合函数的结果。这篇文章通过一个实例来说明这个规则的原因及如何正确书写查询语句。
场景描述
我们有三张表Customers顾客信息、Orders订单信息、OrderItems订单项信息它们分别包含以下字段 Customers 表 cust_id顾客 IDcust_name顾客名称 Orders 表 order_num订单号cust_id顾客 ID与 Customers 表关联 OrderItems 表 order_num订单号与 Orders 表关联quantity商品数量item_price商品单价
需求是返回每个顾客的 cust_name 和与之关联的订单号 order_num以及每个订单的总价 OrderTotal并按顾客名称和订单号进行排序。
数据示例
Customers 表
cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hex
Orders 表
order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217
OrderItems 表
order_numquantityitem_pricea1100010a220010a31015a42550a51525a777
正确的 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_nameorder_numOrderTotalandya110000bena22000tonya3150toma41250ana5375hexa749
错误示例及解析
一个常见的错误查询示例如下
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 表结构如下
idregionsales_amount1East2002East1503West3004West250
我们希望按 region 进行分组求每个地区的总销售额。正确的 SQL 查询应该是
SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;输出结果
regiontotal_salesEast350West550
在这个例子中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 标准所要避免的“不确定性”问题。
如何解决
如果确实需要某个非聚合列出现在查询结果中那么我们有两种选择 将非聚合列加入 GROUP BY 子句中如果 id 是我们希望按某个粒度分组的属性可以将其加入 GROUP BY。 SELECT id, region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY id, region;使用聚合函数处理该列如果想得到某个分组内的特定 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 将无法理解如何处理这些非聚合列可能导致错误。
