5 MySQL 排序、分组

MySQL

MySQL ORDER BY 语句

我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

ORDER BY 语法

SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

select field1,field2,...,fieldN table_name1,table_name2...
order by field1,[field2...] [ASC [DESC]]

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 你可以添加 WHERE...LIKE 子句来设置条件。

实例

mysql> select * from runoob_tbl order by submission_date ASC;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         5 | 学习Python   | RUNOOB.COM    | 2016-03-06      |
|         4 | JAVA教程     | RUNOOB.COM    | 2016-05-06      |
|         1 | 学习 PHP     | 菜鸟教程      | 2017-08-10      |
|         2 | 学习MYSQL    | 菜鸟教程      | 2017-08-10      |
+-----------+--------------+---------------+-----------------+
mysql> select * from runoob_tbl order by submission_date DESC;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2017-08-10      |
|         2 | 学习MYSQL    | 菜鸟教程      | 2017-08-10      |
|         4 | JAVA教程     | RUNOOB.COM    | 2016-05-06      |
|         5 | 学习Python   | RUNOOB.COM    | 2016-03-06      |
+-----------+--------------+---------------+-----------------+

MySQL GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语法

select column_name,function(column_name)
from table_name
WHERE column_name operator value
group by column_name

实例

先将以下数据导入数据库中：

mysql> CREATE TABLE `employee_tbl` (
    -> `id` int(11) NOT NULL,
    -> `name` char(10) NOT NULL DEFAULT '',
    -> `date` datetime NOT NULL,
    -> `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> insert into `employee_tbl` values ('1','小明','2016-04-22 15:25:33','1'),('2','小王','2016-04-20 15:25:47','3'),
    -> ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'),
    -> ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');

导入成功后，执行以下 SQL 语句：

mysql> set names utf8;
mysql> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明   | 2016-04-22 15:25:33 |      1 |
|  2 | 小王   | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽   | 2016-04-19 15:26:02 |      2 |
|  4 | 小王   | 2016-04-07 15:26:14 |      4 |
|  5 | 小明   | 2016-04-11 15:26:40 |      4 |
|  6 | 小明   | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+

接下来使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：

mysql> select name,count(*) from employee_tbl group by name;
+--------+----------+
| name   | count(*) |
+--------+----------+
| 小丽   |        1 |
| 小明   |        3 |
| 小王   |        2 |
+--------+----------+

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> select name,sum(singin) as singin_count from employee_tbl group by name with rollup;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽   |            2 |
| 小明   |            7 |
| 小王   |            7 |
| NULL   |           16 |
+--------+--------------+

其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
以下实例中如果名字为空我们使用总数代替：

mysql> select coalesce(name,'总数'),sum(singin) as singin_count from employee_tbl group by name with rollup;
+-------------------------+--------------+
| coalesce(name,'总数')   | singin_count |
+-------------------------+--------------+
| 小丽                    |            2 |
| 小明                    |            7 |
| 小王                    |            7 |
| 总数                    |           16 |
+-------------------------+--------------+