7.13 SQL Server分组查询
SQL Server GROUP BY
简介
GROUP BY
子句可以将查询结果按照某列或者某几列进行分组。
语法:
SELECT select_list FROM table_name GROUP BY column_name1, column_name2 ,...;
在此查询中,GROUP BY
子句会为GROUP BY
子句中列出的列中的值的每个组合生成一个组。
小技巧: 每什么就以哪个字段进行分组
看如下例子:
SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id;
在本例中,我们检索了客户id为1和2的客户的客户id和订购年份。
从输出中可以清楚地看到,id为1的客户在2016年下了一个订单,2018年下了两个订单。id为2的客户在2017年下了2个订单,在2018年下了一个订单。
添加GROUP BY
子句看看有什么影响:
SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id;
GROUP BY
子句使用客户id和订单年份的唯一组合将前三行分为两组,将后三行分到其他两组。
从功能上讲,上述查询中的GROUP BY
子句产生的结果与使用DISTINCT
子句的以下查询相同:
SELECT DISTINCT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id;
SQL Server分组子句和聚合函数
实际上,GROUP BY
子句通常与聚合函数一起用于生成统计报表。
聚合函数对组执行计算,并为每个组返回唯一值。例如,COUNT()
返回每个组中的行数。其他常用的聚合函数有SUM()
、AVG()
(平均值)、MIN()
(最小值)、MAX()
(最大值)。
GROUP BY
子句将行排列分成成组,聚合函数返回每个组的摘要(数量、最小值、最大值、平均值、总和等)。
例如,以下查询返回客户每年的下订单的数量:
SELECT customer_id, YEAR (order_date) order_year, COUNT (order_id) order_placed FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id;
如果要引用GROUP BY
子句中未列出的列或表达式,则必须将该列用作聚合函数的输入。否则,报错,因为无法保证列或表达式将为每个组返回一个值(每一组中,该列或者表达式可能不唯一)。例如,以下查询直接引用了order_status
,但是这个字段没有出现在GROUP BY
子句中,而且同一组中,手游的订单的order_status
不一定相同,所以SQL Server会报错:
SELECT customer_id, YEAR (order_date) order_year, order_status FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id;
注意,一定要把
GROUP BY
后的数据当作一组一组的看待,而不是一条一条的。
分组示例
使用GROUP BY
和COUNT()
函数
以下查询返回每个城市的客户数量:
SELECT city, COUNT (customer_id) customer_count FROM sales.customers GROUP BY city ORDER BY city;
在本例中,GROUP BY
子句按城市将客户分组,COUNT()
函数返回每个城市的客户数量。
类似地,以下查询按州和城市返回客户数量:
SELECT city, state, COUNT (customer_id) customer_count FROM sales.customers GROUP BY state, city ORDER BY city, state;
使用GROUP BY
和MIN
和MAX
函数
查询每种品牌的产品在2018年的最低和最高标价:
SELECT brand_name, MIN (list_price) min_price, MAX (list_price) max_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name;
在这个例子中,SQL Server先处理WHERE
子句再处理GROUP BY
子句;
使用GROUP BY
和AVG()
函数
以下语句使用AVG()
函数返回每种品牌的产品在2018年的平均标价:
SELECT brand_name, AVG (list_price) avg_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name;
使用GROUP BY
和SUM()
函数
有如下订单项order_items
表:
以下查询使用SUM()
函数获取每个订单的净值(一个订单包含多个项/商品):
其中discount
是折扣
SELECT order_id, SUM ( quantity * list_price * (1 - discount) ) net_value FROM sales.order_items GROUP BY order_id;
HAVING
简介
HAVING
通常与GROUP BY
一起使用,用于根据条件对分组进行过滤。
语法:
SELECT select_list FROM table_name GROUP BY group_list HAVING conditions;
语法中,GROUP BY
子句先将行进行分组,HAVING
子句将一个或多个条件应用于这些组。
结果中只保留满足条件的分组,不满足条件,或者表达式的值为UNKNOWN
的组将会被过滤掉。
由于SQL Server在HAVING
子句之后处理SELECT
子句,因此不能使用列别名引用select列表中指定的聚合函数。请看以下查询,查询将报错:
SELECT column_name1, column_name2, aggregate_function (column_name3) column_alias FROM table_name GROUP BY column_name1, column_name2 HAVING column_alias > value;
必须在HAVING
子句中显式使用聚合函数表达式,如下所示:
SELECT column_name1, column_name2, aggregate_function (column_name3) alias FROM table_name GROUP BY column_name1, column_name2 HAVING aggregate_function (column_name3) > value;
示例
HAVING
与COUNT
函数示例
有如下订单(order)表
以下语句使用HAVING
子句查找每年至少下两个订单的客户:
SELECT customer_id, YEAR (order_date), COUNT (order_id) order_count FROM sales.orders GROUP BY customer_id, YEAR (order_date) HAVING COUNT (order_id) >= 2 ORDER BY customer_id;
本例中:
- 首先,
GROUP BY
子句按每个客户每年对销售订单进行分组。COUNT()
函数的作用是:返回每个客户每年的订单数量。 - 其次,
HAVING
子句过滤掉了订单数量少于两个的所有客户。
HAVING
与SUM
函数示例
有如下订单项(order_items)表:
以下语句查找净值大于20000的销售订单:
SELECT order_id, SUM ( quantity * list_price * (1 - discount) ) net_value FROM sales.order_items GROUP BY order_id HAVING SUM ( quantity * list_price * (1 - discount) ) > 20000 ORDER BY net_value;
本例中:
- 首先,
SUM()
函数返回销售订单的净值。 - 其次,
HAVING
子句过滤掉净值小于或等于20000的销售订单。
HAVING
与MAX
和MIN
函数示例
有如下产品(products)表:
查找每个产品类别的最大和最小标价。然后,过滤出最大标价大于4000或最小标价小于500的类别:
SELECT category_id, MAX (list_price) max_list_price, MIN (list_price) min_list_price FROM production.products GROUP BY category_id HAVING MAX (list_price) > 4000 OR MIN (list_price) < 500;
HAVING
与AVG
函数示例
以下语句查找平均标价介于500和1000之间的产品类别:
SELECT category_id, AVG (list_price) avg_list_price FROM production.products GROUP BY category_id HAVING AVG (list_price) BETWEEN 500 AND 1000;
更多示例
--1.计算每门课程的平均分 select CourseName, avg(skillScore) as 平均分 from StudentScore group by CourseName --2.统计每个学生的平均分 select stuId,avg(skillScore+theoryScore) from StudentScore group by stuId -- 统计每个班级有多少个学生 select ClassId,Count(stuId) as 个数 from StudentInfo group by ClassId -- 统计每门课程有多少位同学在学习 select CourseName,count(stuId) from StudentScore group by CourseName -- 统计每个学生学习了多少门课程 select stuId,count(CourseName) from StudentScore group by stuId --3.查看每一门课程的平均分,总分,最高分,最低分 select CourseName,avg(skillScore),sum(skillScore),max(skillScore),min(skillScore) from StudentScore group by CourseName -- 3.1 查询每门课程,每个学生的最低分 select CourseName,stuId,min(skillScore) from StudentScore group by CourseName,stuId --4.统计每门课程的最低分,并且查询出70分以上的 -- having:在分组的基础之上进行数据过滤 select CourseName,min(skillScore) from StudentScore group by CourseName having min(skillScore)>70 --5.统计每门课程,但不包含课程C# 入门编程的最低分,并且查询出70分以上的 -- 5.1统计每门课程最低分 select CourseName,min(skillScore) from StudentScore group by CourseName -- 5.2 在统计之前加上where 课程!=C# 入门编程 select CourseName,min(skillScore) from StudentScore where CourseName!='C# 入 门编程' group by CourseName -- 5.3 在统计之后加上having 最低分>=70 select CourseName,min(skillScore) from StudentScore where CourseName!='C# 入 门编程' group by CourseName having min(skillScore)>70 --6.查询每门课程学习的人数大于等于2人的课程名称与学习人数。 select CourseName,count(stuId) from StudentScore group by CourseName having count(stuId)>=2 --7.查询不只学了一门课程的学生编号与学习的课程数 -- 翻译成人话:每个学生学习的课程数,并且学习的数量大于1 select stuId,count(CourseName) from StudentScore group by stuId having count(CourseName)>1
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器