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