查询语句 group by 分组
建表语句在最后
- group by 关键字可以根据一个或多个字段对查询结果进行分组
- group by 一般都会结合Mysql聚合函数来使用
- 如果需要指定条件来过滤分组后的结果集,需要结合 having 关键字;原因:where不能与聚合函数联合使用 并且 where 是在 group by 之前执行的
group by 的语法格式
GROUP BY <字段名>[,<字段名>,<字段名>]
先确认测试表里有什么数据,方便后面例子对比
group by 单字段分组例子
对 sex 单个字段进行分组查询
ALTER TABLE test_table MODIFY username varchar(255) AFTER sex;
SELECT * FROM test_table GROUP BY sex;
知识点
分组之后,只会返回组内第一条数据;具体原理可看下图
group by 多字段分组例子
先按照 age 分组,在按照 department 分组
SELECT * FROM test_table GROUP BY department,age;
知识点
- 多个字段分组查询时,先按照第一个字段分组,如果第一个字段有相同值,则把分组结果再按第二个字段进行分组,以此类推
- 如果第一个字段每个值都是唯一的,则不会按照第二个字段在进行分组了,具体原理可看下图
group by + group_concat()的例子
group_concat() 可以将分组后每个组内的值显式出来
SELECT department,GROUP_CONCAT(username) AS '部门员工姓名' FROM test_table GROUP BY department;
可以看到,按department部门分组,然后查看每个部门都有哪些员工的名字;还是很便捷的
group by + 聚合函数的例子
- 统计记录的条数
- sum() :字段值的总和
- max() :字段值的最大值
- min() :字段值的最小值
- avg() :字段值的平均值
具体的例子
SELECT department,COUNT(*) FROM test_table GROUP BY department;
#效果一样
SELECT department,COUNT(1) FROM test_table GROUP BY department;
SELECT department,SUM(age) FROM test_table GROUP BY department;
SELECT department,MAX(age) FROM test_table GROUP BY department;
SELECT department,MIN(age) FROM test_table GROUP BY department;
SELECT department,AVG(age) FROM test_table GROUP BY department;
group by + with rollup的例子
with rollup 用来在所有的记录的最后加上一条记录,显示上面所以记录每个字段的总和(直接看例子)
SELECT GROUP_CONCAT(username) FROM test_table GROUP BY department WITH ROLLUP;
SELECT department,GROUP_CONCAT(username) FROM test_table GROUP BY department WITH ROLLUP;
SELECT department,SUM(age) FROM test_table GROUP BY department WITH ROLLUP;
SELECT department,COUNT(*) FROM test_table GROUP BY department WITH ROLLUP;
CREATE TABLE `test_table` (
`id` int(10) NOT NULL,
`sex` varchar(20) DEFAULT NULL,
`department` varchar(20) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`height` int(10) DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`username` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (1, '男', '钱', 'a', 18, 175, '2021-10-14 10:15:51');
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (2, '男', '李', 'a', 23, 170, '2021-10-14 10:19:44');
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (3, '女', '王', 'b', 14, 185, '2021-10-14 10:19:52');
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (4, '男', '张', 'c', 21, 180, '2021-10-14 10:19:55');
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (5, '女', '谢', 'd', 18, 160, '2021-10-14 10:19:58');
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (6, '女', '郭', 'b', 13, 165, '2021-10-14 10:20:14');
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (7, '男', '陈', 'a', 19, 162, '2021-10-14 10:20:18');
INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (8, '男', '任', 'd', 20, 178, '2021-10-14 10:27:04');