7.13 SQL Server分组查询

 

SQL Server GROUP BY

 

目录

• SQL Server GROUP BY
  • 简介
  • SQL Server分组子句和聚合函数
  • 分组示例
    • 使用GROUP BY和COUNT()函数
    • 使用GROUP BY和MIN和MAX函数
    • 使用GROUP BY和AVG()函数
    • 使用GROUP BY和SUM()函数
  • HAVING
    • 简介
    • 示例
      • HAVING与COUNT函数示例
      • HAVING与SUM函数示例
      • HAVING与MAX和MIN函数示例
      • HAVING与AVG函数示例
  • 更多示例

 

简介

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

 

分类: SQL Server

标签: SQL Server , 数据库