MySQL分组函数

先新建一张表，根据这张表来操作。

新建一张表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for push_message_info
-- ----------------------------
DROP TABLE IF EXISTS `push_message_info`;
CREATE TABLE `push_message_info`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流水ID(主键)',
  `afterSchool` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '经过校门',
  `pushDate` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '推送消息时间',
  `personNo` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 353454 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

插入一些数据

INSERT INTO `push_message_info` VALUES (286150, '4号门进', '2022-04-01 07:04:53', '201705019');
INSERT INTO `push_message_info` VALUES (286151, '4号门进', '2022-04-01 07:04:53', '201705019');
INSERT INTO `push_message_info` VALUES (286152, '4号门进', '2022-04-01 07:18:53', '201806028');
INSERT INTO `push_message_info` VALUES (286153, '1号门进', '2022-04-01 07:32:21', '201806042');
INSERT INTO `push_message_info` VALUES (286154, '1号门进', '2022-04-01 07:32:22', '201806039');
INSERT INTO `push_message_info` VALUES (286155, '1号门进', '2022-04-01 07:37:14', '201806006');
INSERT INTO `push_message_info` VALUES (286156, '1号门进', '2022-04-01 07:39:23', '201806050');
INSERT INTO `push_message_info` VALUES (286157, '1号门进', '2022-04-01 07:39:47', '201806016');
INSERT INTO `push_message_info` VALUES (286158, '1号门进', '2022-04-01 07:40:45', '201601004');
INSERT INTO `push_message_info` VALUES (286159, '1号门进', '2022-04-01 07:43:26', '201806001');
INSERT INTO `push_message_info` VALUES (286160, '1号门出', '2022-04-01 07:41:52', '201806001');
INSERT INTO `push_message_info` VALUES (286161, '1号门出', '2022-04-01 07:41:55', '201806001');
INSERT INTO `push_message_info` VALUES (286162, '4号门出', '2022-04-01 07:44:13', '201806050');
INSERT INTO `push_message_info` VALUES (286163, '4号门出', '2022-04-01 07:44:15', '201806050');
INSERT INTO `push_message_info` VALUES (286164, '4号门出', '2022-04-01 07:44:18', '201806050');
INSERT INTO `push_message_info` VALUES (286165, '4号门出', '2022-04-01 07:44:21', '201806050');
INSERT INTO `push_message_info` VALUES (286166, '4号门出', '2022-04-01 07:44:24', '201806015');
INSERT INTO `push_message_info` VALUES (286167, '3号门出', '2022-04-01 07:44:06', '201806042');
INSERT INTO `push_message_info` VALUES (286168, '4号门出', '2022-04-01 07:44:27', '201806050');
INSERT INTO `push_message_info` VALUES (286169, '4号门出', '2022-04-01 07:44:30', '201806050');
INSERT INTO `push_message_info` VALUES (286170, '4号门出', '2022-04-01 07:44:34', '201806050');
INSERT INTO `push_message_info` VALUES (286171, '4号门出', '2022-04-01 07:44:36', '201806050');
INSERT INTO `push_message_info` VALUES (286172, '4号门出', '2022-04-01 07:44:39', '201806050');
INSERT INTO `push_message_info` VALUES (286173, '4号门出', '2022-04-01 07:44:42', '201806050');
INSERT INTO `push_message_info` VALUES (286174, '3号门出', '2022-04-01 07:44:24', '201806015');
INSERT INTO `push_message_info` VALUES (286175, '4号门出', '2022-04-01 07:44:45', '201806050');
INSERT INTO `push_message_info` VALUES (286176, '4号门出', '2022-04-01 07:44:48', '201806050');

分组函数

什么是分组函数？分组函数作用于一组数据，并对一组数据返回一个值。

组函数类型

• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()

平均函数 AVG()

SELECT AVG(id) from push_message_info ;

--------------------------------------------------
结果：
319801.5000
--------------------------------------------------

计算函数COUNT()

SELECT COUNT(*) from push_message_info WHERE personNo LIKE('2016%');

--------------------------------------------------
结果：
5537
--------------------------------------------------

最大值max()

SELECT MAX(id) from push_message_info WHERE personNo LIKE('2016%');

--------------------------------------------------
结果：
353453
--------------------------------------------------

最小值min()

SELECT MIN(id) from push_message_info WHERE personNo LIKE('2016%');

--------------------------------------------------
结果：
286158
--------------------------------------------------

总和 sum()

SELECT sum(id) from push_message_info WHERE personNo LIKE('2016%');

--------------------------------------------------
结果：
1768109995
--------------------------------------------------

分组数据

可以使用GROUP BY子句将表中的数据分成若干组

SELECT COLUMN
	,
	group_function ( COLUMN ) 
FROM
	TABLE [ WHERE CONDITION ] [ GROUP BY group_by_expression ] [ ORDER BY COLUMN ];

单个分组

SELECT afterSchool,personNo,pushDate,id,SUM(afterSchool) from push_message_info  GROUP BY afterSchool;
--------------------------------------------------
结果：
1号门出	201806001	2022-04-01 07:41:52	286160	6386
1号门进	201806042	2022-04-01 07:32:21	286153	8984
2号门出	201806015	2022-04-01 07:43:07	286188	13230
2号门进	201904031	2022-04-01 07:47:59	286237	20776
3号门出	201806042	2022-04-01 07:44:06	286167	21756
3号门进	202102046	2022-04-01 07:46:36	286212	28449
4号门出	201806050	2022-04-01 07:44:13	286162	34720
4号门进	201705019	2022-04-01 07:04:53	286150	38064
--------------------------------------------------

多个分组

SELECT afterSchool,personNo,pushDate,id,SUM(afterSchool) from push_message_info  GROUP BY afterSchool,pushDate;

--------------------------------------------------
结果：
1号门出	202103033	2022-04-01 16:27:36	288093	1
1号门出	202103033	2022-04-01 16:27:45	288094	1
1号门出	202103033	2022-04-01 16:40:38	288095	1
1号门出	202103003	2022-04-01 16:40:39	288096	2
1号门出	202004035	2022-04-01 17:28:24	288100	1
1号门出	202004032	2022-04-01 17:28:25	288104	1
1号门出	202004004	2022-04-01 17:28:27	288109	2
1号门出	202004027	2022-04-01 17:28:29	288115	3
--------------------------------------------------

数据有点多截取一部分

非法使用组函数

🍕不能在 WHERE 子句中使用组函数。

🍔可以在 HAVING 子句中使用组函数。

where 使用组函数

SELECT afterSchool,personNo,pushDate,id,SUM(afterSchool) from push_message_info WHERE avg(id) >1000;

--------------------------------------------------
结果：
SELECT afterSchool,personNo,pushDate,id,SUM(afterSchool) from push_message_info WHERE avg(id) >1000
> 1111 - Invalid use of group function
> 时间: 0s
--------------------------------------------------

having 使用组函数

SELECT afterSchool,personNo,pushDate,id,SUM(afterSchool) from push_message_info  GROUP BY afterSchool HAVING SUM(afterSchool) >30000;

--------------------------------------------------
结果：
4号门出	201806050	2022-04-01 07:44:13	286162	34720
4号门进	201705019	2022-04-01 07:04:53	286150	38064
--------------------------------------------------