查询语句 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, '-10-14 10:15:51');INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (2, '男', '李', 'a', 23, 170, '-10-14 10:19:44');INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (3, '女', '王', 'b', 14, 185, '-10-14 10:19:52');INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (4, '男', '张', 'c', 21, 180, '-10-14 10:19:55');INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (5, '女', '谢', 'd', 18, 160, '-10-14 10:19:58');INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (6, '女', '郭', 'b', 13, 165, '-10-14 10:20:14');INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (7, '男', '陈', 'a', 19, 162, '-10-14 10:20:18');INSERT INTO `test`.`test_table` (`id`, `sex`, `username`, `department`, `age`, `height`, `date`) VALUES (8, '男', '任', 'd', 20, 178, '-10-14 10:27:04');
