讲解MySQL中的GROUP BY分组和ORDER BY排序

Mysql作为关系型数据库，通过DBeaver和Workbench等工具能够可视化数据，让我们像查看表格一样查看我们存储的数据。 在实际项目开发中，避免不了使用MySQL数据库存储一些重要信息，然后相关开发人员会从数据库中获取数据，并分析这些数据，从而得到有用信息。我想大多数数据分析师经常做这类事情。

当我们从数据库导出数据后，往往会对数据进行分组、筛选等操作。
Mysql中提供了group by关键字进行分组查询操作。

举个例子

创建表

CREATE TABLE test1.student(
	id INT(11) UNSIGNED NOT NULL PRIMARY KEY,
	`number` INT(11) UNSIGNED NOT NULL COMMENT '学号',
	name varchar(64) NOT NULL COMMENT '姓名',
	subclass varchar(64) NOT NULL COMMENT '科目',
	grade INT NOT NULL COMMENT '成绩'
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

注意：编码方式是utf8mb4，排序规则是utf8mb4_general_ci

插入数据

id	number	name	subclass	grade
1	2001	‘Jack’	‘math’	90
2	2001	‘Jack’	‘chinese’	55
3	2002	‘Mark’	‘math’	88
4	2002	‘Mark’	‘chinese’	90
5	2003	‘Jack’	‘math’	56
6	2003	‘Jack’	‘chinese’	90

其中，有同名的学生Jack

小需求

求每门学科中最高的分数

SELECT subclass 学科, MAX(grade) 最高成绩 FROM chengjingx_test1.student GROUP BY 学科 ORDER BY 最高成绩 DESC ;

结果

学科	最高成绩
‘math’	90
‘chinese’	90

首先按照学科分组，然后分组后对应的聚合函数为max，它可以求出最大值，同理，也可以使用min求出最小值。

求出每位学生最高的分数

SELECT `number` 学号, MAX(grade) 最高成绩 
FROM chengjingx_test1.student 
GROUP BY 学号 
ORDER BY 最高成绩 
DESC ;

结果

学号	最高成绩
2001	90
2002	90
2003	90

以学号分组，然后使用max聚合

语法讲解

分组查询语法：
SELECT column, group_function, … FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];

说明：
group_function：聚合函数，它确定了将分组后的数据以何种方式糅合在一起
group_by_expression： 分组表达式， 多个之家用逗号隔开。以哪些字段进行分组
group_condition： 分组之后会数据进行过滤。分组之后同样可以进行条件过滤

聚合函数包括：

-- 创建订单表
-- CREATE TABLE label.t_order(
-- 	id int not null AUTO_INCREMENT COMMENT '订单id',
-- 	user_id bigint not null COMMENT '下单人id',
-- 	user_name varchar(16) not null default '' COMMENT '用户名',
-- 	price decimal(10, 2) not null default 0 COMMENT '订单金额',
-- 	the_year SMALLINT not null COMMENT '订单创建年份',
-- 	PRIMARY KEY (id)
-- ) COMMENT '订单表';

-- 插入数据
-- INSERT into label.t_order (user_id, user_name, price, the_year) values 
-- (1001, '路人甲Java', 11.11, '2017'),
-- (1001, '路人甲Java', 22.22, '2018'),
-- (1001, '路人甲Java', 88.88, '2018'),
-- (1002, '刘德华', 33.33, '2017'),
-- (1002, '刘德华', 12.22, '2018'),
-- (1002, '刘德华', 16.66, '2018'),
-- (1002, '刘德华', 44.44, '2019'),
-- (1003, '张学友', 55.55, '2018'),
-- (1003, '张学友', 66.66, '2019');

SELECT * FROM label.t_order;

-- 单子段分组，查询每个用户下单数量
SELECT user_id 用户id , COUNT(1) 下单数量 FROM label.t_order GROUP BY 1;

-- 多字段分组：查询每个用户每年下单数量，输出字段：用户id、年份、下单数量
-- SELECT user_id 用户id, the_year 年份, COUNT(2) 下单数量 FROM label.t_order GROUP BY 1, 2;
SELECT user_id 用户id, the_year 年份, COUNT(2) 下单数量 FROM label.t_order GROUP BY user_id, the_year;


-- 分组前筛选数据：分组前对数据进行筛选，使用where关键字
-- 需求：需要查询2018年每个用户下单数量，输出：用户id、下单数量
SELECT user_id 用户id, COUNT(1) 下单数量 FROM label.t_order WHERE the_year = 2018 GROUP BY user_id ;


-- 分组后筛选数据：分组后筛选数据，使用having关键字
-- 需求：查询2018年订单数量大于1的用户，输出：用户id, 下单数量
-- SELECT user_id 用户id, COUNT(id) 下单数量 FROM label.t_order WHERE the_year = 2018 GROUP BY user_id HAVING COUNT(id) > 1;
-- 另一种写法
SELECT 
	user_id 用户id, COUNT(id) 下单数量 
FROM 
	label.t_order 
WHERE the_year = 2018 
GROUP BY user_id 
HAVING 下单数量 > 1;

-- 分组后排序
-- 需求：获取每个用户最大金额，然后按照最大金额倒序，输出：用户id,最大金额
SELECT 
	user_id 用户id,
	MAX(price) 最大金额
FROM label.t_order
GROUP BY user_id
ORDER BY 最大金额 desc;


-- where & group by & having & order by & limit
SELECT 列 FORM
表名
WHERE [查询条件]
GROUP BY [分组表达式]
HAVING [分组过滤条件]
ORDER BY [排序条件]
LIMIT [offset, ] count; 

-- 需求：查询出2018年，下单数量大于等于2的，按照下单数量降序排序，最后只输出第一条记录，
-- 显示：用户id, 下单数量
SELECT 
	user_id 用户id,
	COUNT(id) 下单数量 
FROM label.t_order
WHERE the_year = 2018
GROUP BY user_id 
HAVING 下单数量 >= 2
ORDER BY 下单数量 DESC 
LIMIT 1;

-- 分组中select后面的列只能有2种：1 出现在group by后面的列； 2 使用聚合函数的列

-- 需求：获取每个用户下单的最大金额及下单的年份，输出：用户id, 最大金额，年份
SELECT 
	user_id 用户id,
	MAX(price) 最大金额,
	the_year 年份
FROM label.t_order
GROUP BY user_id;

语义讲解模块大部分来自于路人甲Java公众号中关于Mysql的文章，特此感谢。