-- Tips: 聚合和排序
-- 一, 对表进行聚合查询
-- 1. 聚合函数
-- (1)5 个常用函数:
-- COUNT: 计算表中的记录 (行) 数.
-- SUM: 计算表中数值列的数据合计值.
-- AVG: 计算表中数值列的数据平均值.
-- MAX: 求出表中任意列中数据的最大值.
-- MIN: 求出表中任意列中数据的最小值.
-- (2)聚合: 将多行汇总成一行.
--2. 计算表中数据的行数
-- 示例
SELECT COUNT(*) -- *: 参数, 这里代表全部列
- FROM dbo.Conbio;
- --------------------------------------
--3. 计算 NULL 以外数据的行数
-- 将 COUNT(*) 的参数改成指定对象的列, 就可以得到该列的非 NULL 行数.
- SELECT COUNT(Conbio_price2)
- FROM dbo.Conbio;
--[备注] 除了 COUNT 函数, 其它函数不能将星号作为参数.
-- [备注] COUNT 函数的结果根据参数的不同而不同. COUNT(*) 会得到包含 NULL 的数据行数, 而 COUNT(<列名>) 会得到 NULL 之外的数据行数.
--------------------------------------
--4. 计算合计值
select
SUM(Conbio_price1) as sum_Conbio_price1, -- 总和
AVG(Conbio_price1) as avg_Conbio_price1, -- 平均
MAX(Conbio_price1) as max_Conbio_price1, -- 最大值
MIN(Conbio_price1) as min_Conbio_price1 -- 最小值
from dbo.Conbio;
--[备注] 所有的聚合函数, 如果以列名为参数, 会无视 NULL 值所在的行.
------------------
SELECT MAX(Conbio_DATE), --Conbio_DATE 为日期
MIN(Conbio_date)
FROM dbo.Conbio
--[备注] MAX/MIN 函数几乎适用于所有数据类型的列. SUM/AVG 函数只适用于数值类型的列.
--------------------------------------
-- 5. 使用聚合函数删除重复值(关键字 distinct)
-- 示例 1: 计算去除重复数据后的数据行数
- SELECT COUNT(DISTINCT Conbio_varieties)
- FROM dbo.conbio;
- ------------------
-- 示例 2: 先计算数据行数再删除重复数据的结果
- SELECT DISTINCT COUNT(Conbio_Varieties)
- FROM dbo.Conbio;
--[备注] 在聚合函数的参数中使用 DISTINCT(示例 1), 可以删除重复数据. DISTINCT 不仅限于 COUNT 函数, 所有的聚合函数都可以使用.
--------------------------------------
-- 二, 对表进行分组
-- 1.GROUP BY 子句
-- 语法:
--SELECT <列名 1>, <列名 2>, ...
--FROM <表名>
--GROUP BY <列名 1>, <列名 2>, ...;
-- 示例
- SELECT conbio_varieties AS '商品种类',
- COUNT(*) AS '数量'
- FROM dbo.conbio
- GROUP BY conbio_varieties;
--[备注] GROUP BY 子句中指定的列称为 "聚合键" 或 "分组列".
-- [子句的书写顺序(暂定)] SELECT --> FROM --> WHERE --> GROUP BY
------------------
--2. 聚合键中包含 NULL 的情况
- SELECT conbio_price2, COUNT(*)
- FROM dbo.conbio
- GROUP BY conbio_price2;
--[备注] 聚合键中包含 NULL 时, 在结果中也会以 NULL 行的形式表现出来.
--------------------------------------
--3.WHERE 对 GROUP BY 执行结果的影响
-- 语法
--SELECT <列名 1>, <列名 2>, ...
--FROM <表名>
--WHERE <表达式>
--GROUP BY <列名 1>, <列名 2>, ...
- SELECT conbio_price2, COUNT(*)
- FROM dbo.conbio
- WHERE conbio_varieties = '衣服'
- GROUP BY conbio_price2
-- 这里是先根据 WHERE 子句指定的条件进行过滤, 然后再进行聚合处理.
-- [执行顺序] FROM --> WHERE --> GROUP BY --> SELECT. 这里是执行顺序, 跟之前的书写顺序是不一样的.
--------------------------------------
--4. 与聚合函数和 GROUP BY 子句有关的常见错误
-- (1)易错: 在 SELECT 子句中书写了多余的列
-- SELECT 子句只能存在以下三种元素:
-- 常数
-- 聚合函数
-- GROUP BY 子句中指定的列名(即聚合键)
-- 易错点 1
-- [总结] 使用 GROUP BY 子句时, SELECT 子句不能出现聚合键之外的列名.
-- (2)易错: 在 GROUP BY 子句中写了列的别名
-- 回顾之前说的执行顺序, SELECT 子句是在 GROUP BY 子句之后执行. 所以执行到 GROUP BY 子句时无法识别别名.
-- [总结] GROUP BY 子句不能使用 SELECT 子句中定义的别名.
-- (3)易错: GROUP BY 子句的结果能排序吗?
-- [解答] 它是随机的. 如果想排序, 请使用 ORDER BY 子句.
-- [总结] GROUP BY 子句结果的显示是无序的.
--(4)易错: 在 WHERE 子句中使用聚合函数
-- [总结] 只有 SELECT 子句和 HAVING 子句 (以及 ORDER BY 子句) 中能够使用聚合函数.
--------------------------------------
-- 三, 为聚合结果指定条件
-- 1.HAVING 子句
-- WHERE 子句智能指定记录 (行) 的条件, 而不能用来指定组的条件.
-- [备注] HAVING 是 HAVE(拥有)的现在分词.
-- 语法:
--SELECT <列名 1>, <列名 2>, ...
--FROM <表名>
--GROUP BY <列名 1>, <列名 2>, ...
--HAVING <分组结果对应的条件>
--[书写顺序] SELECT --> FROM --> WHERE --> GROUP BY --> HAVING
- SELECT conbio_varieties, COUNT(*)
- FROM dbo.conbio
- GROUP BY conbio_varieties
- HAVING COUNT(*) = 2
- ------------------
--2.HAVING 子句的构成要素
-- (1)3 要素:
-- 常数
-- 聚合函数
-- GROUP BY 子句中指定的列名(即聚合键)
------------------
--3.HAVING 与 WHERE
-- 有些条件可以写在 HAVING 子句中, 又可以写在 WHERE 子句中. 这些条件就是聚合键所对应的条件.
--[建议] 虽然结果一样, 聚合键对应的条件应该写在 WHERE 子句中, 不是 HAVING 子句中.
-- [理由] WHERE 子句的执行速度比 HAVING 快.
-- 意义: WHERE 子句 = 指定行所对应的条件, HAVING 子句 = 指定组所对应的条件.
--------------------------------------
-- 四, 对查询结果进行排序
--1.ORDER BY 子句
-- 语法:
--SELECT <列名 1>, <列名 2>, ...
--FROM <表名>
--ORDER BY <排序基准列 1>, <排序基准列 2>, ...
SELECT conbio_id, conbio_price1
FROM dbo.conbio
ORDER BY conbio_price1; -- 升序排列
-- 排序键: ORDER BY 子句中书写的列名.
--[书写顺序] SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY
------------------
--2. 升序 (ASC) 和降序(DESC):
SELECT conbio_id, conbio_price1
FROM dbo.conbio
ORDER BY conbio_price1 DESC; -- 降序排列
--ORDER BY conbio_id asc; -- 降序排列
--[备注] ORDER BY 子句中排列顺序时会默认使用升序 (ASC) 进行排列.
------------------
--3. 指定多个排序键
SELECT conbio_id, conbio_name, conbio_price1, conbio_price2
FROM dbo.conbio
ORDER BY conbio_price1, conbio_price2;
------------------
--4.NULL 值的顺序: 排序键中包含 NULL 时, 会在开头或末尾进行汇总.
------------------
--5. 在排序键中使用 SELECT 子句中的别名
- SELECT conbio_id AS id, conbio_name, conbio_price1 AS ht
- FROM dbo.conbio
- ORDER BY ht, id;
--[执行顺序] FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY
--[备注] ORDER BY 子句可以使用 SELECT 子句中定义的别名, GROUP BY 子句不能使用别名.
------------------
--6.ORDER BY 子句中使用聚合函数
- SELECT conbio_varieties, COUNT(*)
- FROM dbo.conbio
- GROUP BY conbio_varieties
- ORDER BY COUNT(*);
- ------------------
--7. 不建议使用列的编号进行排序, 虽然可以
- SELECT conbio_id ,
- conbio_name ,
conbio_varieties ,
conbio_price1 ,
conbio_price2 ,
- conbio_date
- FROM dbo.conbio
- ORDER BY conbio_price1 DESC, conbio_id;
- ------------------
- SELECT conbio_id ,
- conbio_name ,
conbio_varieties ,
conbio_price1 ,
conbio_price2 ,
conbio_date
FROM dbo.conbio
ORDER BY 4 DESC, 1; -- 这里使用列的编号, 由于阅读不便, 不推荐使用
--[备注] 在 ORDER BY 子句中不要使用列的编号.
--------------------------------------
来源: http://www.bubuko.com/infodetail-2564244.html