SQL基础-第3章聚合与排序

3-1　对表进行聚合查询

聚合函数

COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值

计算表中数据的行数

-- 计算全部数据的行数
SELECT COUNT(*)
FROM Product;

计算NULL之外的数据的行数

COUNT(*)会得到包含NULL的数据行数
COUNT(<列名>)会得到NULL之外的数据行数

-- 计算NULL之外的数据行数
SELECT COUNT(purchase_price)
FROM Product;

-- 将包含NULL的列作为参数时， COUNT（ *） 和COUNT（<列名>）的结果并不相同
SELECT COUNT(*), COUNT(purchase_price)
FROM Product;

计算合计值

聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。

-- 计算销售单价的合计值
SELECT SUM(sale_price)
FROM Product;

-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price)
FROM Product;

计算平均值

-- 计算销售单价的平均值
SELECT AVG(sale_price)
FROM Product;

-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM Product;

计算值和小值

MAX/MIN函数几乎适用于所有数据类型的列。 SUM/AVG函数只适用于数值类型的列。

-- 计算销售单价的最大值和进货单价的最小值
SELECT MAX(sale_price), MIN(purchase_price)
FROM Product;

-- 计算登记日期的最大值和最小值
SELECT MAX(regist_date), MIN(regist_date)
FROM Product;

使用聚合函数删除重复值（关键字DISTINCT）

想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。

在聚合函数的参数中使用DISTINCT，可以删除重复数据。

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM Product;

-- 先计算数据行数再删除重复数据的结果
SELECT DISTINCT COUNT(product_type)
FROM Product;

-- 使不使用DISTINCT时的动作差异（ SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM Product;

3-2　对表进行分组

GROUP-BY子句

使用GROUP BY子句进行汇总
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;

GROUP BY 子句的书写位置也有严格要求，一定要写在 FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

聚合键中包含NULL的情况

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

-- 按照进货单价统计数据行数
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;

使用WHERE子句时GROUP-BY的执行结果

使用WHERE子句和GROUP BY子句进行汇总处理

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE
GROUP BY <列名1>, <列名2>, <列名3>, ……;

GROUP BY 和 WHERE 并用时，SELECT 语句的执行顺序
FROM → WHERE → GROUP BY → SELECT

-- 同时使用WHERE子句和GROUP BY子句
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;

与聚合函数和GROUP-BY子句有关的常见错误

在SELECT子句中书写了多余的列

使用聚合函数时， SELECT 子句中只能存在以下三种元素。

常数
聚合函数
GROUP BY子句中指定的列名（也就是聚合键）

使用GROUP BY子句时， SELECT子句中不能出现聚合键之外的列名。

-- 在SELECT子句中书写聚合键之外的列名会发生错误
SELECT product_name, purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;

#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.Product.product_name' 
which is not functionally dependent on columns in GROUP BY clause

在GROUP BY子句中写了列的别名
这样的写法在其他 DBMS 中并不是通用的，因此请大家不要使用

-- GROUP BY子句中使用列的别名会引发错误
SELECT product_type AS pt, COUNT(*)
FROM Product
GROUP BY pt;

GROUP BY子句的结果能排序吗
GROUP BY子句结果的显示是无序的
在WHERE子句中使用聚合函数

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

-- 在WHERE子句中使用聚合函数会引发错误
SELECT product_type, COUNT(*)
FROM Product
WHERE COUNT(*) = 2
GROUP BY product_type;
-- ERROR: 不能在WHERE子句中使用聚合

只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

-- DISTINCT和GROUP BY能够实现相同的功能
SELECT DISTINCT product_type
FROM Product;

SELECT product_type
FROM Product
GROUP BY product_type;

3-3　为聚合结果指定条件

HAVING子句

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>

HAVING子句要写在GROUP BY子句之后。

-- 从按照商品种类进行分组后的结果中，取出“包含的数据行数为2行”的组
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;

-- 不使用HAVING子句的情况
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

HAVING子句的构成要素

常数
聚合函数
GROUP BY子句中指定的列名（即聚合键）

-- HAVING子句的不正确使用方法
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_name = '圆珠笔';

ERROR: 列"product,product_name"必须包含在GROUP BY子句当中，或者必须在聚合函数中使用

相对于HAVING子句，更适合写在WHERE子句中的条件

-- 将条件书写在HAVING子句中的情况
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = '衣服';

-- 将条件书写在WHERE子句中的情况
SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;

WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件

WHERE 子句更具速度优势的另一个理由是，可以对 WHERE 子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度

3-4　对查询结果进行排序

ORDER-BY子句

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, ……

子句的书写顺序

SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句

-- 显示商品编号、商品名称、销售单价和进货单价的SELECT语句
SELECT product_id, product_name, sale_price, purchase_price FROM Product;

-- 按照销售单价由低到高（升序）进行排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;

指定升序或降序

未指定ORDER BY子句中排列顺序时会默认使用升序进行排列

-- 按照销售单价由高到低（降序）进行排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;

指定多个排序键

-- 按照销售单价和商品编号的升序进行排序
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;

NULL的顺序

排序键中包含NULL时，会在开头或末尾进行汇总。

-- 按照进货单价的升序进行排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;

在排序键中使用显示用的别名

一定要记住 SELECT 子句的执行顺序在 GROUP BY 子句之后， ORDER BY 子句之前

-- ORDER BY子句中可以使用列的别名
SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
FROM Product
ORDER BY sp, id;

ORDER-BY子句中可以使用的列

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

-- SELECT子句中未包含的列也可以在ORDER BY子句中使用
SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id;

-- ORDER BY子句中也可以使用聚合函数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(*);

不要使用列编号

代码阅读起来比较难
排序功能将来会被删除

-- 通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC, product_id;
-- 通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY 3 DESC, 1;

练习题

3.1 请指出下述 SELECT 语句中所有的语法错误。

-- 本SELECT语句中存在错误。
SELECT product_id, SUM(product_name)
FROM Product
GROUP BY product_type
WHERE regist_date > '2009-09-01';

使用了字符类型的列（product_name）作为 SUM 函数的参数。
SUM 函数只能使用数值类型的列作为参数。
WHERE 子句写在了 GROUP BY 子句之后。
WHERE 子句必须写在 GROUP BY 子句之前。
SELECT 子句中存在 GROUP BY 子句中未指定的列（product_id）。
使用 GROUP BY 子句时，书写在 SELECT 子句中的列有很多限制。 GROUP BY子句中未指定的列不能书写在 SELECT 子句之中。

3.2 请编写一条 SELECT 语句，求出销售单价（ sale_price 列）合计值是进货单价（ purchase_price 列）合计值 1.5 倍的商品种类。执行结果如下所示。

product_type	sum	sum
衣服	5000	3300
办公用品	600	320

SELECT product_type, sum(sale_price), sum(purchase_price)
FROM Product
GROUP BY product_type
HAVING sum(sale_price) > sum(purchase_price) * 1.5
ORDER BY sum(sale_price) DESC;

3.3 此前我们曾经使用 SELECT 语句选取出了 Product（商品）表中的全部记录。当时我们使用了 ORDER BY 子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考 ORDER BY 子句的内容。

执行结果

product_id	product_name	product_type	sale_price	purchase_price	regist_date
0003	运动T恤	衣服	4000	2800
0008	圆珠笔	办公用品	100		2009-11-11
0006	叉子	厨房用具	500		2009-09-20
0001	T恤衫	衣服	1000	500	2009-09-20
0004	菜刀	厨房用具	3000	2800	2009-09-20
0002	打孔器	办公用品	500	320	2009-09-11
0005	高压锅	厨房用具	6800	5000	2009-01-15
0007	擦菜板	厨房用具	880	790	2008-04-28

SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product
ORDER BY regist_date DESC, sale_price;

posted @ 2021-11-19 15:49 小沈曰阅读(154) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了

公告

昵称：小沈曰
园龄： 12年3个月
粉丝： 13
关注： 13

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类 (55)

随笔档案 (55)

文章档案 (3)

2014年7月(3)

小沈曰

勇往直前，理性处事，乐观积极

SQL基础-第3章 聚合与排序

3-1 对表进行聚合查询