Mysql分组数据

最新推荐文章于 2022-04-14 11:41:06 发布
最新推荐文章于 2022-04-14 11:41:06 发布
阅读量434 收藏
点赞数
分类专栏： Mysql笔记 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44957186/article/details/114002706
版权

一、数据分组

数据分组是根据统计研究的需要，将原始数据按照某种标准划分成不同的组别，分组后的的数据称为分组数据。数据分组的方法有单变量值分组和组距分组两种。数据分组的主要目的是观察数据的分布特征，在进行数据分组后再计算出各组中数据出现的频数**，**就形成了一张频数分布表。

二、创建分组

新的子句：GROUP BY

  • 用法：用GROUP BY 指定分组数据
实例：

计算下表每个部门（deptno）的平均年薪（sal×12）

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

SQL：

mysql> select deptno '部门' , avg(sal*12) '平均年薪',max(sal*12) '最高年薪' ,min(sal*12) '最低年薪' from emp group by deptno;
+--------+--------------+--------------+--------------+
| 部门    | 平均年薪      | 最高年薪       | 最低年薪      |
+--------+--------------+--------------+--------------+
|     20 | 26100.000000 |     36000.00 |      9600.00 |
|     30 | 18800.000000 |     34200.00 |     11400.00 |
|     10 | 35000.000000 |     60000.00 |     15600.00 |
+--------+--------------+--------------+--------------+
3 rows in set (0.00 sec)

这里的聚集函数avg，max，min就不是对整个列（14列）进行聚集计算了，而是按照部门分组后聚集计算每个组的数据

三、GROUP　BY使用规范

  • group by 语句可以包含任意数目的列，这使得能过对分组进行嵌套，为数据分组进行更细致的控制。

  • select  deptno,job,avg(sal*12) '平均年薪',max(sal*12) '最高年薪' ,min(sal*12) '最低年薪' from emp group by deptno,job;

  • 如果在group by语句中国嵌套子分组，数据将在最后规定的分组上进行汇总。

  • select  deptno,job,avg(sal*12) '平均年薪',max(sal*12) '最高年薪' ,min(sal*12) '最低年薪' from emp group by deptno,job;
	
+--------+-----------+--------------+--------------+--------------+
| deptno | job       | 平均年薪     | 最高年薪     | 最低年薪     |
+--------+-----------+--------------+--------------+--------------+
|     20 | CLERK     | 11400.000000 |     13200.00 |      9600.00 |
|     30 | SALESMAN  | 16800.000000 |     19200.00 |     15000.00 |
|     20 | MANAGER   | 35700.000000 |     35700.00 |     35700.00 |
|     30 | MANAGER   | 34200.000000 |     34200.00 |     34200.00 |
|     10 | MANAGER   | 29400.000000 |     29400.00 |     29400.00 |
|     20 | ANALYST   | 36000.000000 |     36000.00 |     36000.00 |
|     10 | PRESIDENT | 60000.000000 |     60000.00 |     60000.00 |
|     30 | CLERK     | 11400.000000 |     11400.00 |     11400.00 |
|     10 | CLERK     | 15600.000000 |     15600.00 |     15600.00 |
+--------+-----------+--------------+--------------+--------------+

  • group by子句中列出的每个列都必须是检索列或者有效的表达式，如果在select语句中使用表达式，则必须在group by子句中使用相同的表达式。

  • 除聚集函数以外，select语句中列举的所有列必须在group by语句中给出（顺序自定义）。

mysql> select  job '工作',deptno '部門',avg(sal*12) '平均年薪',max(sal*12) '最高年薪' ,min(sal*12) '最低年薪' from emp group
by job;
/**报错*/
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysqltest.emp.DEPTNO' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  • 如果列中有NULL值，则NULL将作为一个分组返回，如果有多个NULL值，则将他们分为一组。
mysql> select comm , avg(sal*12) '平均年薪',max(sal*12) '最高年薪' ,min(sal*12) '最低年薪' from emp group by comm;
+---------+--------------+--------------+--------------+
| comm    | 平均年薪     | 最高年薪     | 最低年薪     |
+---------+--------------+--------------+--------------+
|    NULL | 28110.000000 |     60000.00 |      9600.00 |
|  300.00 | 19200.000000 |     19200.00 |     19200.00 |
|  500.00 | 15000.000000 |     15000.00 |     15000.00 |
| 1400.00 | 15000.000000 |     15000.00 |     15000.00 |
|    0.00 | 18000.000000 |     18000.00 |     18000.00 |
+---------+--------------+--------------+--------------+
5 rows in set (0.00 sec)
  • group by语句必须出现在where子句之后，order by子句之前。

先找到具体数据，然后再分组排序

  • 使用with rollup 关键子可以显示每个分组汇总级别。

四、过滤分组（HAVING）

如何过滤分组：

mysql> select job ,avg(sal) from emp group by job having avg(sal) > 1000;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

where子句能使用的操作符，having完全都是能使用。

having和where的区别

where子句后面过滤的是行数据，在数据分组之前就进行过滤，having在数据分组之后才过滤。where过滤掉的行不在分组后的数据中。

  • 不过滤：

mysql> select job , sal from emp ;
+-----------+---------+
| job       | sal     |
+-----------+---------+
| CLERK     |  800.00 |
| SALESMAN  | 1600.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2975.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2850.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1500.00 |
| CLERK     | 1100.00 |
| CLERK     |  950.00 |
| ANALYST   | 3000.00 |
| CLERK     | 1300.00 |
+-----------+---------+
14 rows in set (0.00 sec)

  • where过滤一部分

mysql> select job , sal from emp where sal >1200;
+-----------+---------+
| job       | sal     |
+-----------+---------+
| SALESMAN  | 1600.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2975.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2850.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1500.00 |
| ANALYST   | 3000.00 |
| CLERK     | 1300.00 |
+-----------+---------+
11 rows in set (0.00 sec)

  • 按照job分组（group by）：

mysql> select job , avg(sal) from emp where sal >1200 group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
| CLERK     | 1300.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

  • 再次过滤分组后的数据（having）

mysql> select job , avg(sal) from emp where sal >1200 group by job having avg(sal)>1500;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
3 rows in set (0.00 sec)

五、总结SELECT子句顺序

关键字SELECTDISTINCTFROMWHEREGROUP BYHAVINGORDER BYLIMIT
说明要返回的列或者表达式去重目标表行过滤分组说明组级过滤排序说明检索的行数
是否必须使用√×选表时使用×聚集函数使用×××
Erase Me
关注
专栏目录
MySQL 数据分组
hong的博客
10-15 384
数据分组GROUP BY 和 HAVING 子句1、创建分组 GROUP BY2、过滤分组3、分组和排序 学习 MySQL，整理语法知识，可以打开左侧的「MySQL基础」专栏查看全系列笔记 GROUP BY 和 HAVING 子句 按照某种逻辑，对数据进行分组，在每个组分别进行聚集计算 1、创建分组 GROUP BY SELECT id, COUNT(*) AS num FROM list G...
mysql分组排名
阿浪的专栏
03-04 1316
1.创建表并写入测试数据 create table tb_rank_score ( city varchar(20), score int ); insert into tb_rank_score values('sz',89); insert into tb_rank_score values('sz',76); insert into tb_rank_score values('sz',50); insert into tb_rank_score values('sz',89);
Mysql 根据某个字段分组，并将多条记录的某个字段以逗号分隔成一条数据
gao_yuwushengchu的博客
07-28 1322
group_concat()函数 实例： CREATE TABLE `user` ( `id` bigint(22) NOT NULL AUTO_INCREMENT, `uid` bigint(22) NOT NULL COMMENT '用户id', `name` varchar(255) NOT NULL COMMENT '用户姓名', PRIMARY KEY (`id`) USING BTREE, KEY `idx_uid` (`uid`) USING BTREE ) ENGINE=InnoDB
MySQL分组查询统计每个选项的数量
小斐的专栏
04-14 1905
帮客户做个问卷调查，最后需要统计每道问题的每个选项的数量。思路是先按问题分组，然后使用case语句区分每个选项，然后做统计。 SQL语句如下： SELECT examQuestionId, SUM(CASE WHEN answer='非常同意' then 1 else 0 end) as '非常同意', SUM(CASE WHEN answer='有点同意' then 1 else 0 end) as '有点同意', SUM(CASE WHEN answer='中立' then 1 else
mysql数据库分表及实现
gqy133112079的博客
02-18 196
1，水平分割： 例：QQ的登录表。假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1...qq99表。 用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的...
mysql分组取数量_mysql 求一条sql( 按照某字段分组，每组取只20条数据，每个分组可能有100条，或者1,2条数据)...
weixin_39952182的博客
01-13 1032
create tabletask_test(idint primary keyauto_increment,user_name varchar(50) not null,ac_namevarchar(50) not null,task_typetinyint not null,statustinyint not null,isdeletetinyint not null,start_timedat...
MySql分组后随机获取每组一条数据的操作
12-14
在MySQL数据库中，有时我们需要对数据进行分组处理，并从每个分组中随机选取一条记录。这在统计分析或者抽样调查等场景中非常常见。本文将详细介绍如何在MySQL中实现这一操作，以及一些关于随机选取数据的优化方法。...
Mysql取分组后的每组第一条数据
12-14
在MySQL中，当我们需要从分组后的数据中获取每组的第一条或特定顺序的数据时，标准的`GROUP BY`和`ORDER BY`语句并不能直接实现这个需求。这是因为`GROUP BY`语句会先对数据进行分组，然后在每个组内应用`ORDER BY`...
MySQL 分组查询和聚合函数
12-14
2. `aggregate_function(column2)` 是聚合函数，如 AVG(), COUNT(), MAX(), MIN(), SUM()，用于对分组数据进行计算。 3. `table_name` 是要查询的表名。 4. `condition` 是一个可选的WHERE子句，用于筛选要处理的行...
MySQL分组后拿到所有数据_mysql分组后获取若干数据
weixin_42306938的博客
02-10 3259
最近做一个链表分组查询，并且取每个分组的前几位数据的业务例如有商品表，订单表，品类表，商品表中有自己的分类，并且商品表外键关联品类表主键，订单表外键关联商品表中的商品id业务需要，根据当前时间区间进行年月周日，进行时间对比，也就是说分组第一优先为时间，其次为商品分类，再到品类，再到商品中的社群属性，一共分组条件为4组列出关键字段商品表字段goods_id，goods_type，main_categ...
MySQL最常用分组聚合函数
lxw1844912514的博客
09-19 683
一、聚合函数（aggregation function）---也就是组函数　　在一个行的集合（一组行）上进行操作，对每个组给一个结果。常用的组函数：AVG([distinct] expr...
MySQL之分组统计
why404
12-12 868
分组统计 1.语法： select 查询内容 from 表名 [where 条件] [group by 分组字段名1,分组字段名2…] [order by 字段名 asc|desc] [limit] 2.使用举例 a.求每个部门的人数 select deptno,count(*) from emp group by deptno; b.求每个部门的平均工资 select deptno,avg(s...
mysql取每个分组内前n条数据
Winter_Sun灬的博客
06-04 1996
这里是取前1000 条数据 SELECT formName, columnTitle, `value` FROM ( SELECT formName, columnTitle, `value`, IF(@tmpFormName = formName AND @tmpColumnTitle = columnTitle, @rank:=@rank + 1, @rank:=1) as new_rank, @tmpFormName := formName AS tmpFormName, @tmpColumnTi
mysql分组集合_mysql 集合函数，分组查询
weixin_39676242的博客
02-08 208
USE AAA;USE EEE;-- 跨库查询 查询另一个数据库中的表SELECT * FROM aaa.TEACHER;SELECT * FROM aaa.TEACHER WHERE TNAME LIKE '%张%';-- 查询mysql数据库版本SELECT VERSION();-- 分页查询 LIMIT 4 代表从0开始往后查询4条数据SELECT * FROM aaa.TEACHER...
mysql按id分组,mysql：按ID分组，每个ID获得最高优先级
weixin_32795427的博客
01-18 240
I have the following mysql table called "pics", with the following fields and sample data:id vehicle_id filename priority1 45 a.jpg 42 45 b.jpg 13 56...
mysql分组取每组前几条记录(排序)
偶尔一节
06-25 1719
CREATE TABLE `mygoods` ( `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `cat_id` int(11) NOT NULL DEFAULT '0', `price` tinyint(3) NOT NULL DEFAULT '0', `status` tinyint(3) DEFAUL
Mysql 根据某个字段的一部分进行分组以及总数统计
m0_60628363的博客
11-30 2177
mysql按id分组_mysql-如何根据条件对结果进行分组？
weixin_39872395的博客
02-28 615
我无法解决这个问题很多小时.这是我的桌子t1:–––––––––––––––––––––––––––––––––––| id | text | lang | transl_id |–––––––––––––––––––––––––––––––––––| 1 | first | en | 222 |–––––––––––––––––––––––––––––––––––| ...
解决mysql新版本分组 SELECT list is not in GROUP BY clause 问题
yinger_0131的博客
09-11 3748
报错： Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘t.content’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with s...
mysql分组查询数据归类
最新发布
06-06
MySQL分组查询可以根据指定的列对数据进行分组，并对分组后的数据进行聚合计算。常见的聚合函数包括SUM、AVG、MAX、MIN、COUNT等。 例如，假设我们有一个名为students的表，其中包含学生的姓名(name)、年龄(age)、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值