浅谈MySQL中的group by

目录
  • 1、前言
  • 2、准备user表
    • 2.1 group by规则
    • 2.2 group by使用
    • 2.3 having使用
    • 2.4 order by与limit
    • 2.5 with rollup

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使用的相关规则

  • 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使用

需求:

统计不同民族的用户数

语句:

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)

到此这篇关于浅谈MySQL中的group by的文章就介绍到这了,更多相关MySQL中的group by内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL group by语句如何优化

    在MySQL中,新建立一张表,该表有三个字段,分别是id,a,b,插入1000条每个字段都相等的记录,如下: mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(1

  • mysql之group by和having用法详解

    GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表. select子句中的列名必须为分组列或列函数,列函数对于group by子句定义的每个组返回一个结果. 某个员工信息表结构和数据如下: id name dept salary edlevel hiredate 1 张三 开发部 2000 3 2009-10-11 2 李四 开发部 2500 3 2009-10-01 3 王五 设计部 2600 5 2010-10-02 4 王六 设计部 2300 4

  • MySQL Group by的优化详解

    一个标准的 Group by 语句包含排序.分组.聚合函数,比如 select a,count(*) from t group by a ;  这个语句默认使用 a 进行排序.如果 a 列没有索引,那么就会创建临时表来统计 a和 count(*),然后再通过 sort_buffer 按 a 进行排序. 标准的执行流程 结构: create table t1(id int primary key, a int, b int, index(a)); delimiter ;; create proce

  • 基于mysql实现group by取各分组最新一条数据

    前言: group by函数后取到的是分组中的第一条数据,但是我们有时候需要取出各分组的最新一条,该怎么实现呢? 本文提供两种实现方式. 一.准备数据 http://note.youdao.com/noteshare?id=dba748092a619be0a8f160ccf6e25a5f&sub=FD4C1C7823CA440DB360FEA3B4A905CD 二.三种实现方式 1)先order by之后再分组: SELECT * FROM (SELECT * from tb_dept ORDE

  • mysql高效查询left join和group by(加索引)

    mysql高效查询 mysql牺牲了group by来增加left join的速度(前提是加了索引). user表:10万数据 实例1: 200秒左右 SELECT U.id, A.favorite_count FROM (SELECT id from user) U LEFT JOIN ( -- 点赞数 SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count FROM favorite GROUP BY favo

  • MySQL数据库分组查询group by语句详解

    一:分组函数的语句顺序 1 SELECT ... 2 FROM ... 3 WHERE ... 4 GROUP BY ... 5 HAVING ... 6 ORDER BY ... 二:WHERE和HAVING筛选条件的区别 数据源 位置 关键字 WHERE 原始表 ORDER BY语句之前 WHERE HAVING 分组后的结果集 ORDER BY语句之后 HAVING 三:举例说明 #1.查询每个班学生的最大年龄 SELECT MAX(age),class FROM STU_CLASS GR

  • MySQL group by和order by如何一起使用

    假设有一个表:reward(奖励表),表结构如下: CREATE TABLE test.reward ( id int(11) NOT NULL AUTO_INCREMENT, uid int(11) NOT NULL COMMENT '用户uid', money decimal(10, 2) NOT NULL COMMENT '奖励金额', datatime datetime NOT NULL COMMENT '时间', PRIMARY KEY (id) ) ENGINE = INNODB A

  • mysql group by 对多个字段进行分组操作

    在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组, 用来获取数据表中以分组字段为依据的统计数据. 比如有一个学生选课表,表结构如下: Table: Subject_Selection Subject Semester Attendee --------------------------------- ITB001 1 John ITB001 1 Bob ITB001 1 Mickey ITB001 2 Jenny ITB001 2 James MKB114 1 John MKB

  • 浅谈MySQL中的group by

    目录 1.前言 2.准备user表 2.1 group by规则 2.2 group by使用 2.3 having使用 2.4 order by与limit 2.5 with rollup 1.前言 MySQL的group by用于对查询的数据进行分组:此外MySQL提供having子句对分组内的数据进行过滤. MySQL提供了许多select子句关键字, 它们在语句中的顺序如下所示: 子句 作用 是否必须/何时使用 select 查询要返回的数据或者表达式 是 from 指定查询的表 否 w

  • 浅谈MySQL中group_concat()函数的排序方法

    group_concat()函数的参数是可以直接使用order by排序的.666.. 下面通过例子来说明,首先看下面的t1表. 比如,我们要查看每个人的多个分数,将该人对应的多个分数显示在一起,分数要从高到底排序. 可以这样写: SELECT username,GROUP_CONCAT(score ORDER BY score DESC) AS myScore FROM t1 GROUP BY username; 效果如下: 以上这篇浅谈MySQL中group_concat()函数的排序方法就

  • 浅谈mysql中concat函数,mysql在字段前/后增加字符串

    MySQL中concat函数 使用方法: CONCAT(str1,str2,-) 返回结果为连接参数产生的字符串.如有任何一个参数为NULL ,则返回值为 NULL. 注意: 如果所有参数均为非二进制字符串,则结果为非二进制字符串. 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串. 一个数字参数被转化为与之相等的二进制字符串格式:若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col) MySQ

  • 浅谈mysql中多表不关联查询的实现方法

    大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据

  • 浅谈MySQL中授权(grant)和撤销授权(revoke)用法详解

    MySQL 赋予用户权限命令的简单格式可概括为: grant 权限 on 数据库对象 to 用户 一.grant 普通数据用户,查询.插入.更新.删除 数据库中所有表数据的权利 grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant update on testdb.* to common_user@'%' grant delete on testdb.* to c

  • 浅谈Mysql中类似于nvl()函数的ifnull()函数

    IFNULL(expr1,expr2) 如果expr1不是NULL,IFNULL()返回expr1,否则它返回expr2.IFNULL()返回一个数字或字符串值,取决于它被使用的上下文环境. mysql> select IFNULL(1,0); -> 1 mysql> select IFNULL(0,10); -> 0 mysql> select IFNULL(1/0,10); -> 10 mysql> select IFNULL(1/0,'yes'); ->

  • 浅谈MySQL中的自增主键用完了怎么办

    在面试中,大家应该经历过如下场景 面试官:"用过mysql吧,你们是用自增主键还是UUID?" 你:"用的是自增主键" 面试官:"为什么是自增主键?" 你:"因为采用自增主键,数据在物理结构上是顺序存储,性能最好,blabla-" 面试官:"那自增主键达到最大值了,用完了怎么办?" 你:"what,没复习啊!!"    (然后,你就可以回去等通知了!) 这个问题是一个粉丝给我提的,我觉得

  • 浅谈MySQL中float、double、decimal三个浮点类型的区别与总结

    下表中规划了每个浮点类型的存储大小和范围: 类型 大小 范围(有符号) 范围(无符号) 用途 ==float== 4 bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值 ==double== 8 bytes (-1.797 693 134 862 315 7 E

  • 浅谈MYSQL中树形结构表3种设计优劣分析与分享

    目录 简介 问题 设计1:邻接表 表设计 SQL示例 设计2:路径枚举 表设计 SQL示例 设计3:闭包表 表设计 SQL示例 结合使用 表设计 总结 简介 在开发中经常遇到树形结构的场景,本文将以部门表为例对比几种设计的优缺点: 问题 需求背景:根据部门检索人员, 问题:选择一个顶级部门情况下,跨级展示当前部门以及子部门下的所有人员,表怎么设计更合理 ? 递归吗 ?递归可以解决,但是势必消耗性能 设计1:邻接表 注:(常见父Id设计) 表设计 CREATE TABLE `dept_info01

  • 浅谈mysql中int(1)和int(10)的区别

    目录 困惑 数据说话 零填充 总结 困惑 最近遇到个问题,有个表的要加个user_id字段,user_id字段可能很大,于是我提mysql工单alter table xxx ADD user_id int(1).领导看到我的sql工单,于是说:这int(1)怕是不够用吧,接下来是一通解释. 其实这不是我第一次遇到这样的问题了,其中不乏有工作5年以上的老司机.包括我经常在也看到同事也一直使用int(10),感觉用了int(1),字段的上限就被限制,真实情况肯定不是这样. 数据说话 我们知道在mys

随机推荐