当前位置: 首页 > news >正文

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_idcust_name
cust10andy
cust1ben
cust2tony
cust22tom
cust221an
cust2217hex

Orders 表:

order_numcust_id
a1cust10
a2cust1
a3cust2
a4cust22
a5cust221
a7cust2217

OrderItems 表:

order_numquantityitem_price
a1100010
a220010
a31015
a42550
a51525
a777

正确的 SQL 查询

为了实现需求,我们需要将三张表进行连接,并计算每个订单的总价。计算总价的方法是将 quantityitem_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_idCustomersOrders 表连接,通过 order_numOrdersOrderItems 表连接,获取顾客、订单和订单项的完整信息。
  • 计算订单总价:使用 SUM(quantity * item_price) 计算每个订单的总金额。
  • 分组与排序GROUP BY c.cust_name, o.order_num 按顾客名称和订单号分组,使每个分组对应一个顾客的一个订单,从而计算出每个订单的总金额。ORDER BY c.cust_name, o.order_num 对结果按顾客名称和订单号排序。

查询结果

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

cust_nameorder_numOrderTotal
andya110000
bena22000
tonya3150
toma41250
ana5375
hexa749

错误示例及解析

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

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_amount
1East200
2East150
3West300
4West250

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

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

输出结果

regiontotal_sales
East350
West550

在这个例子中,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 将无法理解如何处理这些非聚合列,可能导致错误。

相关文章:

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

在 SQL 查询中,当我们对数据进行分组时,通常会用到 GROUP BY 子句。SQL 标准要求:所有非聚合列(即没有使用聚合函数的列)都必须出现在 GROUP BY 子句中,或者是聚合函数的结果。这篇文章通过一个实例来说明这…...

Unity中实现战斗帧同步的高级技术

一、帧同步的基本原理 帧同步(Frame Synchronization)在网络游戏中指的是在每一帧上保证所有玩家所看到的游戏状态一致,而不是每个玩家单独计算自己的状态。实现帧同步通常需要每个客户端仅发送用户输入到服务器,并由服务器进行全…...

Qt 正则表达式提取文件中的 USB 设备 ID

Qt 正则表达式提取文件中的 USB 设备 ID flyfish 文档内容 Bus: 001 Device: 001 Description: 1d6b:0002 Linux Foundation 2.0 root hub Bus: 002 Device: 003 Description: 0e0f:0002 , Inc. USB Hub Bus: 002 Device: 002 Description: 0e0f:0003 , Inc. Mouse Bus: 002…...

使用 Python 和 OpenCV 实现摄像头人脸检测并截图

概述 在现代应用中,人脸检测是一项非常重要的技术,广泛应用于安全监控、身份验证等领域。本文将详细介绍如何使用 Python 和 OpenCV 库实现摄像头人脸检测并截图,并通过具体的代码示例来展示整个过程。 环境准备 在开始编写代码之前&#…...

【二叉搜素树】——LeetCode二叉树问题集锦:6个实用题目和解题思路

文章目录 计算布尔二叉树的值求根节点到叶节点的数字之和二叉树剪枝验证二叉搜索树二叉搜索树中第K小的元素二叉树的所有路径 计算布尔二叉树的值 解题思路: 这是一个二叉树的布尔评估问题。树的每个节点包含一个值,其中叶子节点值为 0 或 1&#xff0…...

【计算机视觉】FusionGAN

1. FusionGAN论文阅读 abreheret/FusionGAN: Pytorch implementation of "Generating a Fusion Image: One’s Identity and Another’s Shape" 1.1. WHY 在现实世界中,将对象或人物转换为期望的形状是一种常用技术,但现有的图像翻译方法在处理身份和形状时存在…...

问:SQL优化,七条实践总结?

SQL语句优化是数据库性能调优的重要部分,通过合理的优化可以显著提升查询速度和系统性能。文章总结几种常见SQL语句优化方法。 1. 优化Where子句的顺序 原则:表之间的连接条件应写在其他Where条件之前,能够过滤掉最大数量记录的条件应优先写…...

unity单例模式的不同声明(待完善

总结: 这段代码实现了一个泛型单例模式(Singleton Pattern),用于确保某个类(由泛型参数 T 指定)在整个应用程序中只有一个实例,并且在第一次访问时才创建该实例。该模式保证了该实例的全局唯一…...

大模型在蓝鲸运维体系应用——蓝鲸运维开发智能助手

本文来自腾讯蓝鲸智云社区用户: CanWay 背景 1、运维转型背景 蓝鲸平台从诞生之初,就一直在不遗余力地推动运维转型,让运维团队可以通过一体化PaaS平台,快速编写脚本,编排流程,开发运维工具,从被动地提供…...

vue2,vue3响应式的理解

vue2的话主要使用的是defineProperty对已有属性添加get,set,从而完成对数据的响应式控制,但每次需要for循环对属性进行遍历 function DefineReactive(target, key, value) {//存在多层嵌套的objectObserver(value);Object.defineReactive(target, key, {get() {retu…...

群控系统服务端开发模式-应用开发-前端退出功能

我们从未登录一直到退出,现在已经登录到操作,现在完成退出。退出有两种情况下会退出:第一种情况下是手动点击退出按钮,第二种情况下是登录过期时间到了自动退出的。 一、手动退出 因退出及个人信息页面都在公有页面,所…...

Web入门

Spring 官网:Spring | Home Spring是一个开源的Java企业级应用开发框架。Spring的主要目的是使Java EE(Java Platform, Enterprise Edition)开发更容易,并且通过提供一系列丰富的库和接口来促进良好编程实践,是…...

基于SpringBoot网上超市的设计与实现录像

基于SpringBoot网上超市的设计与实现录像 SpringBoot网上超市的设计与实现录像...

python爬虫(二)爬取国家博物馆的信息

import requests from bs4 import BeautifulSoup# 起始网址 url https://www.chnmuseum.cn/zx/xingnew/index_1.shtml # 用于存储所有数据 all_data [] page 1 global_index 1 # 定义全局序号变量并初始化为1 while True:html_url requests.get(url).textif requests.get…...

【mysql的当前读和快照读】

在MySQL中,尤其是InnoDB存储引擎中,读操作主要分为两种:当前读(Current Read)和快照读(Snapshot Read) 当前读 当前读每次读取的都是当前最新的数据。这种读操作在读取数据时不允许其他事务对这…...

[CKS] Audit Log Policy

最近准备花一周的时间准备CKS考试,在准备考试中发现有一个题目关于audit policy的题目。 ​ 专栏其他文章: [CKS] Create/Read/Mount a Secret in K8S-CSDN博客[CKS] Audit Log Policy-CSDN博客 -[CKS] 利用falco进行容器日志捕捉和安全监控-CSDN博客[CKS] K8S Netw…...

【Linux】-学习笔记03

第十一章-管理Linux软件包和进程 1.源码下载安装软件 1.1概念 源码文件:程序编写者使用C或C等语言编写的原始代码文本文件 源码文件使用.tar.gz或.tar.bz2打包成压缩文件 1.2特点 源码包可移植性好,与待安装软件的工作环境依赖性不大 由于有编译过程…...

Leetcode热题100-32 最长有效括号

Leetcode热题100-32 最长有效括号 1. 题目描述2. 解题思路动态规划栈解法 3. 代码实现动态规划栈解法 1. 题目描述 32 最长有效括号 2. 解题思路 动态规划 定义状态: 设 dp[i] 表示以位置 i 结尾的最长有效括号子串的长度。 状态转移方程: 遍历字符…...

【大数据学习 | HBASE】hbase的读数据流程与hbase读取数据

1. hbase的读数据流程 在解析读取流程之前我们还需要知道两个功能性的组件和HFIle的格式信息 HFILE 存储在hdfs中的hbase文件,这个文件中会存在hbase中的数据以kv类型显示,同时还会存在hbase的元数据信息,包括整个hfile文件的索引大小&…...

A027-基于Spring Boot的农事管理系统

🙊作者简介:在校研究生,拥有计算机专业的研究生开发团队,分享技术代码帮助学生学习,独立完成自己的网站项目。 代码可以查看文章末尾⬇️联系方式获取,记得注明来意哦~🌹 赠送计算机毕业设计600…...

C++:std::is_convertible

C++标志库中提供is_convertible,可以测试一种类型是否可以转换为另一只类型: template <class From, class To> struct is_convertible; 使用举例: #include <iostream> #include <string>using namespace std;struct A { }; struct B : A { };int main…...

java 实现excel文件转pdf | 无水印 | 无限制

文章目录 目录 文章目录 前言 1.项目远程仓库配置 2.pom文件引入相关依赖 3.代码破解 二、Excel转PDF 1.代码实现 2.Aspose.License.xml 授权文件 总结 前言 java处理excel转pdf一直没找到什么好用的免费jar包工具,自己手写的难度,恐怕高级程序员花费一年的事件,也…...

《从零掌握MIPI CSI-2: 协议精解与FPGA摄像头开发实战》-- CSI-2 协议详细解析 (一)

CSI-2 协议详细解析 (一&#xff09; 1. CSI-2层定义&#xff08;CSI-2 Layer Definitions&#xff09; 分层结构 &#xff1a;CSI-2协议分为6层&#xff1a; 物理层&#xff08;PHY Layer&#xff09; &#xff1a; 定义电气特性、时钟机制和传输介质&#xff08;导线&#…...

OkHttp 中实现断点续传 demo

在 OkHttp 中实现断点续传主要通过以下步骤完成&#xff0c;核心是利用 HTTP 协议的 Range 请求头指定下载范围&#xff1a; 实现原理 Range 请求头&#xff1a;向服务器请求文件的特定字节范围&#xff08;如 Range: bytes1024-&#xff09; 本地文件记录&#xff1a;保存已…...

2025盘古石杯决赛【手机取证】

前言 第三届盘古石杯国际电子数据取证大赛决赛 最后一题没有解出来&#xff0c;实在找不到&#xff0c;希望有大佬教一下我。 还有就会议时间&#xff0c;我感觉不是图片时间&#xff0c;因为在电脑看到是其他时间用老会议系统开的会。 手机取证 1、分析鸿蒙手机检材&#x…...

Map相关知识

数据结构 二叉树 二叉树&#xff0c;顾名思义&#xff0c;每个节点最多有两个“叉”&#xff0c;也就是两个子节点&#xff0c;分别是左子 节点和右子节点。不过&#xff0c;二叉树并不要求每个节点都有两个子节点&#xff0c;有的节点只 有左子节点&#xff0c;有的节点只有…...

有限自动机到正规文法转换器v1.0

1 项目简介 这是一个功能强大的有限自动机&#xff08;Finite Automaton, FA&#xff09;到正规文法&#xff08;Regular Grammar&#xff09;转换器&#xff0c;它配备了一个直观且完整的图形用户界面&#xff0c;使用户能够轻松地进行操作和观察。该程序基于编译原理中的经典…...

Vite中定义@软链接

在webpack中可以直接通过符号表示src路径&#xff0c;但是vite中默认不可以。 如何实现&#xff1a; vite中提供了resolve.alias&#xff1a;通过别名在指向一个具体的路径 在vite.config.js中 import { join } from pathexport default defineConfig({plugins: [vue()],//…...

Python 高效图像帧提取与视频编码:实战指南

Python 高效图像帧提取与视频编码:实战指南 在音视频处理领域,图像帧提取与视频编码是基础但极具挑战性的任务。Python 结合强大的第三方库(如 OpenCV、FFmpeg、PyAV),可以高效处理视频流,实现快速帧提取、压缩编码等关键功能。本文将深入介绍如何优化这些流程,提高处理…...

k8s从入门到放弃之HPA控制器

k8s从入门到放弃之HPA控制器 Kubernetes中的Horizontal Pod Autoscaler (HPA)控制器是一种用于自动扩展部署、副本集或复制控制器中Pod数量的机制。它可以根据观察到的CPU利用率&#xff08;或其他自定义指标&#xff09;来调整这些对象的规模&#xff0c;从而帮助应用程序在负…...