查询语句 group by 分组

查询语句 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');