亲宝软件园·资讯

展开

MySQL中的group by 浅谈MySQL中的group by

李子捌 人气:0
想了解浅谈MySQL中的group by的相关内容吗,李子捌在本文为您仔细讲解MySQL中的group by的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:MySQL中的group,by,group,by,下面大家一起来学习吧。

1、前言

MySQLgroup 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 '民族',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `height` double NULL DEFAULT NULL COMMENT '身高',
  `sex` 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, '李子捌', '汉族', 18, 180, 1);
INSERT INTO `user` VALUES (2, '张三', '回族', 20, 175, 1);
INSERT INTO `user` VALUES (3, '李四', '维吾尔族', 45, 168, 0);
INSERT INTO `user` VALUES (4, '王五', '蒙古族', 18, 177, 1);
INSERT INTO `user` VALUES (5, '赵六', '汉族', 16, 184, 0);
INSERT INTO `user` VALUES (6, '田七', '维吾尔族', 27, 192, 1);

user表中数据如下所示:

mysql> select * from user;
+----+--------+----------+------+--------+------+
| id | name   | nation   | age  | height | sex  |
+----+--------+----------+------+--------+------+
|  1 | 李子捌 | 汉族     |   18 |    180 |    1 |
|  2 | 张三   | 回族     |   20 |    175 |    1 |
|  3 | 李四   | 维吾尔族 |   45 |    168 |    0 |
|  4 | 王五   | 蒙古族   |   18 |    177 |    1 |
|  5 | 赵六   | 汉族     |   16 |    184 |    0 |
|  6 | 田七   | 维吾尔族 |   27 |    192 |    1 |
+----+--------+----------+------+--------+------+
6 rows in set (0.00 sec)

2.1 group by规则

使用group by之前需要先了解group by使用的相关规则

2.2 group by使用

需求:

统计不同民族的用户数

语句:

mysql> select nation, count(*) from user group by nation;
+----------+----------+
| nation   | count(*) |
+----------+----------+
| 汉族     |        2 |
| 回族     |        1 |
| 维吾尔族 |        2 |
| 蒙古族   |        1 |
+----------+----------+
4 rows in set (0.00 sec)

group by可以结合where一起使用,不过where不能在group by之后进行过滤,使用where子句之后,分组的数据是where子句过滤后的数据集。

mysql> select nation, count(*) as nation_num  from user where sex = 0 group by nation;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 维吾尔族 |          1 |
| 汉族     |          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 = '汉族';
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 = '汉族'' 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 = '汉族';
+--------+------------+
| nation | nation_num |
+--------+------------+
| 汉族   |          2 |
+--------+------------+
1 row in set (0.00 sec)

2.4 order by与limit

分组后的数据需要排序可以使用order byorder by子句需要更在having子句之后。

mysql> select nation, count(*) as nation_num  from user group by nation having nation != '汉族' order by nation_num desc;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 维吾尔族 |          2 |
| 回族     |          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 != '汉族' order by nation_num desc limit 2;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 维吾尔族 |          2 |
| 回族     |          1 |
+----------+------------+
2 rows in set (0.00 sec)

2.5 with rollup

在group by子句中,WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)

比如max():

mysql> select nation, max(height) as nation_num  from user group by nation with rollup;
+----------+------------+
| nation   | nation_num |
+----------+------------+
| 回族     |        175 |
| 汉族     |        184 |
| 维吾尔族 |        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         |
+----------+--------------------+
| 回族     |                175 |
| 汉族     |                182 |
| 维吾尔族 |                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 |
+----------+------------+
| 回族     |          1 |
| 汉族     |          2 |
| 维吾尔族 |          2 |
| 蒙古族   |          1 |
| NULL     |          6 |
+----------+------------+
5 rows in set (0.00 sec)

加载全部内容

相关教程
猜你喜欢
用户评论