sql排序取前三_SQL基础三：聚合与排序

3-1 对表进行聚合查询

聚合函数定义：用于汇总的函数成为聚合函数或者聚集函数。聚合即将多行汇总为一行。

常用的5个函数：

COUNT：计算表中的记录数（行数）

例：计算全部数据的行数

SELECT COUNT(*) FROM Product;

COUNT()中的星号代表全部列，括号中的输入值成为参数或parameter，输出值称为返回值。

想要计算NULL之外的数据的行数，通过将对象列设定为参数来实现。

结果根据参数的不同而不同，COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

SUM：计算表中数值列中数据的合计值

SELECT SUM(<列名>) FROM Product;

语法与COUNT函数相同，但不能使用星号作为参数。在使用SUM时，NULL值是被排除在外的，即无论有多少个NULL值都会被忽略。

AVG：计算表表中数值列中数据的平均值

SELECT AVG(<列名>) FROM Product;

计算时会事先删除NULL再计算。

SUM和AVG函数只能对数值类型的列使用。

MAX：求出表中任意列中数据的最大值

MIN：求出表中任意列中数据的最小值

MAX和MIN函数原则上适用于任何类型的列，即只要能排序的数据，肯定会有最值。

SELECT MAX(<列名>) FROM Product;
SELECT MIN(<列名>) FROM Product;

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

3-2 对表进行分组

1、GROUP BY

使用GROUP BY子句可以像切蛋糕那样将表分割。语法：

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

例：按照商品种类统计数据行数

SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;

GROUP BY子句中指定的列称为聚合键或者分组列，和SELECT子句一样，可以通过逗号分隔指定多列。

当聚合键中包含NULL时，也会将NULL作为一组特定的数据，在结果中会以“不确定”行（空行）的形式表现出来。

SELECT purchase_price, COUNT(*)
FROM product
GROUP BY purchase_price;

GROUP BY子句一定要写在FROM语句之后，如果有WHERE子句就写在WHERE子句之后。

使用WHERE子句时GROUP BY进行汇总，会先根据WHERE子句指定条件进行过滤，然后再进行汇总处理。

语法：

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

例：

SELECT purchase_price, COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;

执行顺序：FROM-WHERE-GROUP BY-SELECT

使用聚合函数和GROUP BY子句时需要注意以下4点：

• 只能写在SELECT子句中
• GROUP BY子句中不能使用SELECT子句中列的别名
• GROUP BY子句的聚合结果是无序的
• WHERE 子句中不能使用聚合函数

2、GROUP BY+WITH CUBE

CUBE指定在结果集内不仅包含GROUP BY提供的行，还包含汇总行，汇总行在结果中显示为NULL，但用来表示所有值。结果集内的汇总行数取决于GROUP BY子句内包含的列数。CUBE返回每个可能的组和子组组合。

在mysql 5.6.17版本中，只定义了cube，但是不支持cube操作

SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
WITH CUBE;

报错：

3、GROUP BY+WITH ROLLUP

ROLLUP根据维度在数据结果集中进行的聚合操作。指定在结果集内不仅包含GROUP BY提供的行，还包含汇总行，按层次结构顺序，从组内的最低级到最高级汇总组。组的层次结构取决于列分组时指定使用的顺序。更改分组顺序会影响在结果集内生成的行数。

CUBE和ROLLU均不支持区分聚合函数。

假设用户需要对N个维度进行聚合查询操作，普通的group by语句需要N个查询和N次group by操作。

而rollup的优点是一次可以得出N次group by的结果，这样可以提高查询效率，同时大大减少网络的传输流量。

例：

（1）单个维度：

不加WITH ROLLUP

SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;

加WITH ROLLUP

SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
WITH ROLLUP;

和普通的GROUP BY差别不大，只是多了一个(null,8)，表示对所有的product_type再做一次聚合，即订单数量总和。对单个唯独进行ROLLUP操作只是可以在最后得到聚合的数据，对比GROUP BY语句并没有非常大的优势。

（2）多个维度

不加WITH ROLLUP

SELECT product_type, regist_date, COUNT(*)
FROM product
GROUP BY product_type,regist_date

加WITH ROLLUP

SELECT product_type, regist_date, COUNT(*)
FROM product
GROUP BY product_type,regist_date
WITH ROLLUP;

(null,null)表示最后的聚合

(product_type,null)表示仅对(product_type)一列进行分组的聚合结果

(product_type,regist_date)表示对(product_type,regist_date)两列进行分组的聚合结果，也就是group by本身聚集。

所以，上面结果等价于：

SELECT product_type, regist_date, COUNT(*)
FROM product
GROUP BY product_type,regist_date
UNION
SELECT product_type, NULL, COUNT(*)
FROM product
GROUP BY product_type
UNION
SELECT NULL, NULL, COUNT(*) 
FROM product

注意：

• ORDER BY不能在rollup中使用，两者为互斥关键字；
• 如果分组的列包含NULL值，那么rollup的结果可能不正确，因为在rollup中进行的分组统计时，null具有特殊意义。因此在进行rollup时可以先将null转换成一个不可能存在的值，或者没有特别含义的值，比如：IFNULL(xxx,0)
• mysql中没有像oracle那样的grouping()函数；

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

使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。

语法：

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

HAVING子句要写在GROUP BY后边

使用HAVING子句时SELECT语句的顺序：

SELECT-FROM-WHERE-GROUP BY-HAVING

例：从按照商品种类进行分组后的结果中，取出“包含数据行数为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筛选出行数只包含2行数据的组。

HAVING子句能够使用的3种要素为：

• 常数
• 聚合函数
• GROUP BY子句中指定的列名

HAVING和WHERE的区别

• WHERE子句中不能使用聚合函数，是一个约束声明，使用WHERE来约束来之数据库的数据，WHERE是在结果返回之前起作用的；HAVING子句中可以使用聚合函数，是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作；
• WHERE子句指定行所对应的条件，HAVING子句指定组所对应的条件；
• 在查询过程中聚合语句(SUM,MIN,MAX,AVG,COUNT)要比HAVING子句优先执行。而WHERE子句在查询过程中执行优先级高于聚合语句；
• 在使用Count函数等对表中的数据进行聚合操作时，DBMS内部会进行排序处理，而排序操作会增加机器的负担，减少排序的行数，可以增加处理速度。使用Where子句指定条件时，由于排序之前就对数据进行了过滤，所以能够减少排序的数据量。但是Having子句是在排序之后才对数据进行分组的，因此与前者相比，需要排序的数据量就要多得多。使用Where子句更具速度优势的另一个理由是，可以对Where子句指定条件所对应的列创建索引，这样可以大幅提高处理速度。

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

ORDER BY

即便是同一条SELECT语句，每次执行时排列顺序很可能发生改变。SELECT语句末尾添加ORDER BY来明确指定排列顺序。

语法：

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

ORDER BY后边的列，可以指定多个列，此子句中不能使用ntext，text和image列。

ASC表示升序，DESC表示降序，如果不写，默认为升序。

ORDER BY子句通常写在SELECT语句的末尾。

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

可以使用SELECT子句中定义的别名。

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

不能使用列的编号。

例：

SELECT product_type, regist_date
FROM product
ORDER BY product_type, regist_date;

未加排序：

SELECT product_type, regist_date
FROM product;