浅谈MySQL中的group by
目录
- 1、前言2、准备user表
-
2.1 group by规则2.2 group by使用2.3 having使用2.4 order by与limit2.5 with rollup
1、前言
MySQL的group by用于对查询的数据进行分组；此外MySQL提供having子句对分组内的数据进行过滤。
MySQL提供了许多select子句关键字，
它们在语句中的顺序如下所示：
| 子句 | 作用 | 是否必须/何时使用 |
| select | 查询要返回的数据或者表达式 | 是 |
| from | 指定查询的表 | 否 |
| where | 指定行级过滤 | 否 |
| group by | 分组 | 否/对数据分组时使用 |
| having | 分组过滤 | 否/对分组后的数据过滤使用 |
| order by | 返回数据时指定排序规则 | 否 |
| limit | 指定返回数据的行数 | 否 |
2、准备user表
准备一张user表，其DDL和表数据如下所示
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
— —————————-
— Table structure for user
— —————————-
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户名’,
`nation` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘X’,
`age` int(11) NULL DEFAULT NULL COMMENT ‘年龄’,
`height` double NULL DEFAULT NULL COMMENT ‘身高’,
`sеx` smallint(6) NULL DEFAULT NULL COMMENT ‘性别’,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
— —————————-
— Records of user
— —————————-
INSERT INTO `user` VALUES (1, ‘李子捌’, ‘X’, 18, 180, 1);
INSERT INTO `user` VALUES (2, ‘张三’, ‘X’, 20, 175, 1);
INSERT INTO `user` VALUES (3, ‘李四’, ‘X尔族’, 45, 168, 0);
INSERT INTO `user` VALUES (4, ‘王五’, ‘蒙古族’, 18, 177, 1);
INSERT INTO `user` VALUES (5, ‘赵六’, ‘X’, 16, 184, 0);
INSERT INTO `user` VALUES (6, ‘田七’, ‘X尔族’, 27, 192, 1);
user表中数据如下所示：
mysql> select * from user;
+—-+——–+———-+——+——–+——+
| id | name | nation | age | height | sеx |
+—-+——–+———-+——+——–+——+
| 1 | 李子捌 | X | 18 | 180 | 1 |
| 2 | 张三 | X | 20 | 175 | 1 |
| 3 | 李四 | X尔族 | 45 | 168 | 0 |
| 4 | 王五 | 蒙古族 | 18 | 177 | 1 |
| 5 | 赵六 | X | 16 | 184 | 0 |
| 6 | 田七 | X尔族 | 27 | 192 | 1 |
+—-+——–+———-+——+——–+——+
6 rows in set (0.00 sec)
2.1 group by规则
使用group by之前需要先了解group by使用的相关规则
-
group by子句置于where之后，order by子句之前having 子句置于group by 之后，order by子句之前group by子句中的每个列都必须是select的检索列或者有效表达式，不能使用聚集函数select中使用的表达式，在group by子句中必须出现，并且不能使用别名group by分组的数据中包含null值，null值被分为一组group by子句可以嵌套，嵌套的分组在最后分组上汇总
2.2 group by使用
需求：
统计不同X的用户数
语句：
mysql> select nation, count(*) from user group by nation;
+———-+———-+
| nation | count(*) |
+———-+———-+
| X | 2 |
| X | 1 |
| X尔族 | 2 |
| 蒙古族 | 1 |
+———-+———-+
4 rows in set (0.00 sec)
group by可以结合where一起使用，不过where不能在group by之X行过滤，使用where子句之后，分组的数据是where子句过滤后的数据集。
mysql> select nation, count(*) as nation_num from user where sеx = 0 group by nation;
+———-+————+
| nation | nation_num |
+———-+————+
| X尔族 | 1 |
| X | 1 |
+———-+————+
2 rows in set (0.00 sec)
2.3 having使用
对group by分组后的数据还需要再次过滤，就必须使用having子句。group by子句后使用where子句MySQL服务器会抛出异常
mysql> select nation, count(*) as nation_num from user group by nation where nation = ‘X’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘where nation = ‘X” at line 1
此时只需要将上面where子句替换成having子句即可，having子句支持所有的where操作符，通俗的说where子句能用的地方只有替换成having就可以在group by子句后使用了
vmysql> select nation, count(*) as nation_num from user group by nation having nation = ‘X’;
+——–+————+
| nation | nation_num |
+——–+————+
| X | 2 |
+——–+————+
1 row in set (0.00 sec)
2.4 order by与limit
分组后的数据需要排序可以使用order by，order by子句需要更在having子句之后。
mysql> select nation, count(*) as nation_num from user group by nation having nation != ‘X’ order by nation_num desc;
+———-+————+
| nation | nation_num |
+———-+————+
| X尔族 | 2 |
| X | 1 |
| 蒙古族 | 1 |
+———-+————+
3 rows in set (0.00 sec)
对于输出的结果需要指定返回的行数，可以使用limit，limit子句在整个语句的最后。
mysql> select nation, count(*) as nation_num from user group by nation having nation != ‘X’ order by nation_num desc limit 2;
+———-+————+
| nation | nation_num |
+———-+————+
| X尔族 | 2 |
| X | 1 |
+———-+————+
2 rows in set (0.00 sec)
2.5 with rollup
在group by子句中，WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,АVG,COUNT…）
比如max()：
mysql> select nation, max(height) as nation_num from user group by nation with rollup;
+———-+————+
| nation | nation_num |
+———-+————+
| X | 175 |
| X | 184 |
| X尔族 | 192 |
| 蒙古族 | 177 |
| NULL | 192 |
+———-+————+
5 rows in set (0.00 sec)
比如avg()：
mysql> select nation, avg(height) as nation_num from user group by nation with rollup;
+———-+——————–+
| nation | nation_num |
+———-+——————–+
| X | 175 |
| X | 182 |
| X尔族 | 180 |
| 蒙古族 | 177 |
| NULL | 179.33333333333334 |
+———-+——————–+
5 rows in set (0.00 sec)
比如count()：
mysql> select nation, count(*) as nation_num from user group by nation with rollup;
+———-+————+
| nation | nation_num |
+———-+————+
| X | 1 |
| X | 2 |
| X尔族 | 2 |
| 蒙古族 | 1 |
| NULL | 6 |
+———-+————+
5 rows in set (0.00 sec)
到此这篇关于浅谈MySQL中的group by的文章就介绍到这了,更多相关MySQL中的group by内容请搜索共生网络以前的文章或继续浏览下面的相关文章希望大家以后多多支持共生网络！
原创文章，作者：starterknow，如若转载，请注明出处：https://www.starterknow.com/117831.html
starterknow