8、表中数据的排序与分组_MySQL

bitsCN.com

在前面介绍的使用SQL语句执行查询操作时，我们可能发现查询出的数据结果的排序是无序的。为了更好的观察数据表中的查询结果，开发人员或者用户经常要对查询的数据进行排序操作，这就需要使用ORDER BY子句。在数据库的实际应用中，有时需要对查询的数据进行统计和分组操作，这就需要了解SQL语句的聚合函数和GROUP BY子句的使用。有些时候开发人员或者用户还希望对分组后的结果做进一步的统计，在SQL语句中提供了ROLLUP这样一个关键字用来对数据进行统计。最后还将介绍主要数据库中如何限制结果集的行数。

1.使用ORDER BY子句对数据记录进行排序

指定表中的一列进行排序
通过ORDER BY 子句可以对查询结果中指定的列进行升序或者是降序操作，这取决于ORDER BY子句后的关键字，如果ORDER BY子句后面的关键字是ASC，则对查询的结果执行升序操作；如果ORDER BY子句后面的关键字是DESC，则对查询的结果执行降序操作。其语法规则如下
ORDER BY 列名1 [ASC|DESC]
其中列名1表示需要对该列进行排序操作。关键字ASC和DESC是可选的。如果ORDER BY 子句后面不写ASC或者是DESC，则默认执行的是升序操作。

SELECT teaID,teaName,dept,profession,salaryFROM T_teacher WHERE dept ='计算机系'ORDER BY salary ASC

指定表中列的位置序号进行排序
排在使用ORDER BY子句进行排序操作时，除了可以使用列名对指定列进行序，也可以使用该列在选择列表中的位置的序号对其进行排序。

SELECT teaID,teaName,dept,profession,salaryFROM T_teacher ORDER BY 5 ASC

对SELECT语句中的非选择列进行排序
ORDER BY子句中也可以对没有在SELECT语句中出现的选择列进行排序操作。

SELECT teaID,teaName,dept,professionFROM T_teacher WHERE dept ='计算机系'ORDER BY salary

指定表中的多列进行排序
ORDER BY子句除了可以指定单列进行排序操作，也可以指定数据表中的多个列进行排序操作。如果要指定数据表中的多个列进行排序操作，则指定排序的列与列之间需要用逗号隔开。其语法规则如下：
ORDER BY 列名1[ASC|DESC],列名2 [ASC|DESC]
其中列名1和列名2表示需要对指定的数据列进行排序操作。列名1和列名2之间用逗号进行分割。关键字ASC和DESC是可选的。如果ORDER BY 子句后面不写ASC或者DESC，则默认执行的是升序操作。首先，根据ORDER BY中指定的第一列进行排序；然后，再根据ORDER BY子句中指定的第二列的升序或者降序方式进行排序。

SELECT teaID,teaName,dept,profession,salaryFROM T_teacher ORDER BY salary DESC,dept ASC

2.常用的聚合函数

聚合函数也被称为分组函数或者统计函数，主要用于对得到的一组数据进行统计计算，例如求和、求平均值等，常用的聚合函数包括COUNT、MAX、MIN、SUM和AVG五个。
COUNT、SUM和AVG函数中可以使用DISTINCT关键字去除指定列中的重复项。使用DISTINCT关键字后只是对不同行的值进行统计。
MAX和MIN函数中的列或者表达式可以是数字型、字符型或者是日期类型的值。如果MAX和MIN函数中的列或者表达式是字符型的，则按照首字母从A到Z的顺序排序，如果首字母相同，则比较字符串中第二个字母的大小，以此类推。汉字则是按照其汉语拼音的全拼来排序。

SELECT MAX(salary),MIN(salary)FROM T_teacher

SUM和AVG函数中的表达式只能是数字类型的值。
除了COUNT(*)之外，其他的几个函数在计算时都忽略表达式中的空值（NULL行）。
COUNT函数是用来计算数据表中的总行数，SUM函数是用来计算数据表中某一列的属性值的总和。

SELECT SUM(salary),COUNT(salary),AVG(salary)FROM T_teacher

聚合函数只能出现在SELECT语句、GROUP BY子句以及HAVING子句中，WHERE子句中不能出现聚合函数。

3.使用GROUP ＢＹ子句对表中数据进行分组

单列分组
使用GROUP BY子句对数据表中的某一列进行分组时，会对指定分组的列中不同的值都计算出一个统计结果。其语法格式如下：
GROUP BY列名1
其中列名1表示需要对该列进行分组操作。

SELECT dept,COUNT(profession)FROM T_teacherGROUP BY dept

Select同时包含数据列和聚合函数时,必须使用Group By。
多列分组
使用GROUP BY子句对数据表中的多个列进行分组时，会对指定分组的多个列中不同的值都计算出一个统计结果。其语法格式如下：
GROUP BY列名1,列名2…
其中列名1和列名2表示需要对指定列进行分组操作。列名1和列名2之间用逗号进行分割。
使用HAVING子句子限制分组后的查询结果
如果想要对分组后的结果限制查询条件，就需要使用HAVING子句。由于HAVING子句是用来限制分组后的查询结果，所以该子句需要放到GROUP BY子句的后面使用。其语法格式如下：
GROUP BY列名1 HAVING 条件表达式
其中列名1表示需要对该列进行分组操作。HAVING子句后的条件表达式是用来筛选分组后的结果。在HAVING子句中经常使用聚合函数对分组后的结果进行筛选。

SELECT dept,profession,MAX(salary)FROM T_teacherGROUP BY dept,professionHAVING MAX(salary)>3000

注意：GROUP BY下才有HAVING，HAVING用于组；而WHERE是针对SELECT的（针对于表或者视图），WHERE用于分布前。

SELECT profession,MAX(salary)FROM T_teacherWHERE age>30GROUP BY professionHAVING MAX(salary)>3000

对分组结果进行排序
很多时候，对数据表中数据进行分组后，还希望对分组的结果进行排序操作。如果想对使用了GROUP BY子句的分组结果进行排序的话，就需要使用ORDER BY子句。

SELECT dept,profession,MAX(salary)FROM T_teacherGROUP BY deptORDER BY MAX(salary) DESC

按照GROUP BY后分得的各组中的最高工资给组排序；而如下语句则是按照每组的第一行的工资给组排序。

SELECT dept,profession,MAX(salary)FROM T_teacherGROUP BY deptORDER BY salary DESC

GROUP BY子句中处理NULL值
在使用GROUP BY子句对对指定列进行分组时，有时可能会遇到指定列中含有NULL值的情况。此时，GROUP BY子句会将该列中所有的NULL值归为一组。
如果要得到每个分组中的工资的最大值对应的行（而不是分组的第一行），可以使用子查询等方法，详细以后讨论。

select teaName,salaryfrom (select * from t_teacher order by salary desc) tempgroup by dept<br>order by salary

4.使用ROLLUP关键字统计数据

在实际应用中，有时不仅需要得到分组后的统计结果，还希望对分组的统计结果做进一步的计算，例如通过对教师信息表（T_teacher）中的院校和教师职称进行分组，得到分组后教师的工资，还希望对每一个院系中的教师的工资做一个阶段性的统计，希望得到各个院校中不同职称的教师的工资的加和（相当于小计），还希望得到所有院校不同职称教师工资的总和（相当于总计）。这个时候仅仅使用GROUP BY子句是无法做到的，此时就需要使用ROLLUP关键字。
ROLLUP关键字使用时需要放到GROUP BY关键字的后面。ROLLUP关键字在不同的数据库中的使用方式上稍有不同。
a.在MySQL和Microsoft SQL Server数据库中需要使用WITH ROLLUP。其语法格式如下：

GROUP BY 列名1 WITH ROLLUP

其中列名1表示要对该列进行分组，WITH ROLLUP关键字表示要对分组的结果进行统计。当然也可以对多个列进行分组，并统计分组后的结果。其语法格式如下：
GROUP BY 列名1 ,列名2 WITH ROLLUP
b.在Oracle数据库中，ROLLUP关键字需要紧跟在GROUP BY关键字的后面，然后再写需要分组的字段。其语法格式如下：
GROUP BY ROLLUP (列名1,列名2…)

5.限制结果集行数

有些时候，开发人员或者用户并不希望将查询结果的数据列中的数据全部显示出来，而是只希望显示其中的几行，尤其是在需要分页的操作中。例如，一个数据表最后查询出了100条记录，而开发人员或者用户只关心其中前10条记录的值，这就需要对查询结果中的数据记录的行数进行限制。在不同的数据库中限制结果集行数的方法也不尽相同。
a.在MySQL数据库中限制结果集行数可以使用LIMIT关键字，它可以用来限制查询出来的数据结果的个数。通过使用LIMIT关键字可以让开发人员或者用户得到其中想要的部分的结果。如果要使用LIMIT限制结果集行数，可以使用下面的语法格式。
LIMIT n
其中LIMIT是关键字，数字n表示要限制结果集行数。

SELECT teaID,teaName,dept,professionFROM T_teacherORDER BY teaIDLIMIT 3-- 升序排序后的前3条记录LIMIT 3,3-- 升序排序后的第4条到第6条记录

b.Oracle数据库中不支持类似于 MySQL 中的 LIMIT关键字来限制结果集行数，但是在 Oracle数据库中可以使用ROWNUM关键字限制结果集的行数。其语法格式如下：
WHERE ROWNUM其中ROWNUM关键字表示对符合条件结果的序列号，它的起始值总是从1开始的。数字n表示要限制的结果集的行数。当然，这里的比较运算符除了可以使用（<）小于以外，还可以使用（<=）小于等于。
c.MySQL数据库和Oracle数据库中使用LIMIT关键字和ROWNUM的方法限制结果集行数，在Microsoft SQL Server数据库中需要使用TOP关键字。其语法格式如下：
SELECT TOP n [PRECENT] 列名1,列名2 …
FROM 表名
…
其中，TOP是表示限制结果集行数的关键字；数字n表示限制结果集行数；PRECENT关键字表示返回查询的结果集中前n%的行数，它是可选的。

bitsCN.com