MySQL-分组查询(GROUP BY)及二次筛选(HAVING)
发布日期:2021-06-28 20:27:38
浏览次数:3
分类:技术文章
本文共 6008 字,大约阅读时间需要 20 分钟。
MySQL-分组查询(GROUP BY)及二次筛选(HAVING)
为了测试GROUP BY 语句,我们创建两张表,并往表中添加数据– 创建部门表
CREATE TABLE IF NOT EXISTS department( id TINYINT UNSIGNED AUTO_INCREMENT KEY, depName VARCHAR(20) NOT NULL UNIQUE ); – 添加部门 INSERT department(depName) VALUES(‘开发部’); INSERT department(depName) VALUES(‘视频部’); INSERT department(depName) VALUES(‘教学部’); INSERT department(depName) VALUES(‘运营部’); – 创建员工表 CREATE TABLE IF NOT EXISTS employee( id Int UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL, age TINYINT UNSIGNED DEFAULT 18, addr VARCHAR(50) NOT NULL DEFAULT ‘北京’, salary FLOAT(6,2) NOT NULL DEFAULT 0, sex ENUM(‘男’,‘女’,‘保密’), depId TINYINT UNSIGNED );– 添加员工记录
INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘张三’,‘21’,‘山东’,‘5432.12’,‘男’,1); INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘李四’,‘32’,‘河北’,‘6432.00’,‘男’,2); INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘王五’,‘26’,‘北京’,‘5932.92’,‘女’,3); INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘赵六’,‘32’,‘上海’,‘6232.14’,‘男’,4); INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘Mr Adword’,‘55’,‘美国’,‘9432.99’,‘男’,4); INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘田七’,‘19’,‘北京’,‘4932.92’,‘保密’,1); INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘孙八’,‘62’,‘上海’,‘9932.14’,‘男’,2); INSERT employee(username,age,addr,salary,sex,depId) VALUES(‘Mr lili’,‘45’,‘美国’,‘9132.99’,‘女’,1);– 创建省份表
CREATE TABLE IF NOT EXISTS provinces( -> id TINYINT UNSIGNED AUTO_INCREMENT KEY, -> pname VARCHAR(10) NOT NULL UNIQUE -> );– 添加省份记录
INSERT provinces(pname) VALUES(‘山东’),(‘河北’),(‘北京’),(‘上海’),(‘美国’);mysql> SELECT * FROM department;
±—±--------+ | id | depName | ±—±--------+ | 1 | 开发部 | | 3 | 教学部 | | 2 | 视频部 | | 4 | 运营部 | ±—±--------+ 4 rows in set (0.06 sec) mysql> SELECT * FROM employee; ±—±----------±-----±-----±--------±-----±------+ | id | username | age | addr | salary | sex | depId | ±—±----------±-----±-----±--------±-----±------+ | 1 | 张三 | 21 | 山东 | 5432.12 | 男 | 1 | | 2 | 李四 | 32 | 河北 | 6432.00 | 男 | 2 | | 3 | 王五 | 26 | 北京 | 5932.92 | 女 | 3 | | 4 | 赵六 | 32 | 上海 | 6232.14 | 男 | 4 | | 5 | 田七 | 19 | 北京 | 4932.92 | 保密 | 1 | | 6 | Mr Adword | 55 | 美国 | 9432.99 | 男 | 4 | | 7 | 田七 | 19 | 北京 | 4932.92 | 保密 | 1 | | 8 | 孙八 | 62 | 上海 | 9932.14 | 男 | 2 | | 9 | Mr lili | 45 | 美国 | 9132.99 | 女 | 1 | ±—±----------±-----±-----±--------±-----±------+ mysql> SELECT * FROM provinces; ±—±------+ | id | pName | ±—±------+ | 4 | 上海 | | 3 | 北京 | | 1 | 山东 | | 2 | 河北 | | 5 | 美国 | ±—±------+1、只使用GROUP BY语句查询结果只显示每一组的一条记录:
mysql> – 按照性别分组 mysql> SELECT * FROM employee GROUP BY sex; ±—±---------±-----±-----±--------±-----±------+ | id | username | age | addr | salary | sex | depId | ±—±---------±-----±-----±--------±-----±------+ | 1 | 张三 | 21 | 山东 | 5432.12 | 男 | 1 | | 3 | 王五 | 26 | 北京 | 5932.92 | 女 | 3 | | 5 | 田七 | 19 | 北京 | 4932.92 | 保密 | 1 | ±—±---------±-----±-----±--------±-----±------+ 3 rows in set (0.05 sec)mysql> – 按照部门编号分组
mysql> SELECT * FROM employee GROUP BY depId; ±—±---------±-----±-----±--------±-----±------+ | id | username | age | addr | salary | sex | depId | ±—±---------±-----±-----±--------±-----±------+ | 1 | 张三 | 21 | 山东 | 5432.12 | 男 | 1 | | 2 | 李四 | 32 | 河北 | 6432.00 | 男 | 2 | | 3 | 王五 | 26 | 北京 | 5932.92 | 女 | 3 | | 4 | 赵六 | 32 | 上海 | 6232.14 | 男 | 4 | ±—±---------±-----±-----±--------±-----±------+ 4 rows in set (0.00 sec)mysql> – 根据多个字段分组
mysql> SELECT * FROM employee GROUP BY sex,depId; ±—±---------±-----±-----±--------±-----±------+ | id | username | age | addr | salary | sex | depId | ±—±---------±-----±-----±--------±-----±------+ | 1 | 张三 | 21 | 山东 | 5432.12 | 男 | 1 | | 2 | 李四 | 32 | 河北 | 6432.00 | 男 | 2 | | 4 | 赵六 | 32 | 上海 | 6232.14 | 男 | 4 | | 9 | Mr lili | 45 | 美国 | 9132.99 | 女 | 1 | | 3 | 王五 | 26 | 北京 | 5932.92 | 女 | 3 | | 5 | 田七 | 19 | 北京 | 4932.92 | 保密 | 1 | ±—±---------±-----±-----±--------±-----±------+2、分组查询配合GROUP_CONCAT()来使用,可以看到每个组中的详细信息:
mysql> – 按照性别分组,得到每组中人员的名称
mysql> SELECT *,GROUP_CONCAT(username) FROM employee GROUP BY sex; ±—±---------±-----±-----±--------±-----±------±------------------------------+ | id | username | age | addr | salary | sex | depId | GROUP_CONCAT(username) | ±—±---------±-----±-----±--------±-----±------±------------------------------+ | 1 | 张三 | 21 | 山东 | 5432.12 | 男 | 1 | 张三,李四,赵六,Mr Adword,孙八 | | 3 | 王五 | 26 | 北京 | 5932.92 | 女 | 3 | 王五,Mr lili | | 5 | 田七 | 19 | 北京 | 4932.92 | 保密 | 1 | 田七,田七 | ±—±---------±-----±-----±--------±-----±------±------------------------------+3、配合聚合函数来使用
COUNT():统计记录的数目 SUM():求字段的和 AVG():求字段的平均值 MAX():求字段的最大值 MIN():求字段的最小值 mysql> – 统计员工表中员工数目,以及薪水的总和、最大值、最小值、平均值 mysql> SELECT id AS ‘编号’,username AS ‘用户名’,COUNT() AS ‘员工总数’,SUM(salary) AS ‘总薪水’,MAX(s alary) AS ‘最高薪水’,MIN(salary) AS ‘最低薪水’,AVG(salary) AS ‘平均薪水’ FROM employee; *************************** 1. row *************************** 编号: 1 用户名: 张三 员工总数: 9 总薪水: 62393.14 最高薪水: 9932.14 最低薪水: 4932.92 平均薪水: 6932.571126 1 row in set (0.00 sec) mysql> – 按照性别分组,统计出每个组中年龄最大值、最小值,薪水最大值,每个组中的人数,人名,以及平均薪水。 mysql> SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT() AS total_peo,AVG(salary) AS avg_salary ,GROUP_CONCAT(username)FROM employee GROUP BY sex; ±—±-----±--------±--------±-----------±----------±------------±------------------------------+ | id | sex | max_age | min_age | max_salary | total_peo | avg_salary | GROUP_CONCAT(username) | ±—±-----±--------±--------±-----------±----------±------------±------------------------------+ | 1 | 男 | 62 | 21 | 9932.14 | 5 | 7492.278027 | 张三,李四,赵六,Mr Adword,孙八 | | 3 | 女 | 45 | 26 | 9132.99 | 2 | 7532.955078 | 王五,Mr lili | | 5 | 保密 | 19 | 19 | 4932.92 | 2 | 4932.919922 | 田七,田七 | ±—±-----±--------±--------±-----------±----------±------------±------------------------------+4、使用HAVING 对分组结果进行二次筛选
mysql> – 按照性别分组,并找到分组后组中人数大于3的组
mysql> SELECT id,sex,COUNT() AS total_peo FROM employee GROUP BY sex HAVING COUNT()>3; ±—±-----±----------+ | id | sex | total_peo | ±—±-----±----------+ | 1 | 男 | 5 | ±—±-----±----------+转载地址:https://blog.csdn.net/yajie_12/article/details/102951500 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
留言是一种美德,欢迎回访!
[***.207.175.100]2024年04月06日 02时24分46秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
大厂架构师经验分享!我离职后面试收割小米等大厂offer,知乎上已获万赞
2019-04-29
安卓开发视频教学!10天用Flutter撸了个高仿携程App,内含福利
2019-04-29
安卓主板开发!Android之内存泄漏调试学习与总结,社招面试心得
2019-04-29
安卓前端开发框架!完美讲解内存缓存LruCache实现原理,吐血整理
2019-04-29
安卓前端开发框架!轻松获得一线大厂面试offer,附答案
2019-04-29
安卓前端开发!Android性能优化之APK优化,赶快收藏备战金九银十!
2019-04-29
安卓定制系统开发!这是一份面向Android开发者的复习指南,系列篇
2019-04-29
安卓客户端开发!如何试出一个Android开发者真正的水平?分享PDF高清版
2019-04-29
安卓平板app开发!实战讲述Flutter跨平台框架应用,附大厂真题面经
2019-04-29
安卓开发包!大佬手把手教你如何仿写出大厂的APP,含BATJM大厂
2019-04-29
字节Android高工面试:BAT等大厂必问技术面试题,成功定级腾讯T3-2
2019-04-29
学习安卓开发!Android初级开发是如何一步步成为高级开发?全网独家首发!
2019-04-29
安卓app二次开发!阿里P7Android社招面试的经历,大厂直通车!
2019-04-29
安卓app开发方案!Android项目开发如何设计整体架构?已开源
2019-04-29
安卓app开发难不难!字节跳动Android实习面试凉凉经,薪资翻倍
2019-04-29
安卓app开发语言!深入浅出Android性能调优,最强技术实现
2019-04-29
7年老Android一次操蛋的面试经历,灵魂拷问
2019-04-29
7年老Android一次操蛋的面试经历,讲的明明白白!
2019-04-29
9次Android面试经验总结,手慢无
2019-04-29
Activity的6大难点,你会几个?年薪50W
2019-04-29