MySQL分组group by详解

1. MySQL分组概述

group by 字段分组，以哪个字段分组就是以哪个字段作为唯一值进行分组，多个字段同样道理。
having相当于是分组后的条件语句。

2. MySQL分组语法结构

select   字段    from   表名   where    条件     group   by       字段
select   字段    from   表名   group  by    字段    having    过滤条件
注意：对于过滤条件，可以先用where，再用group  by或者是先用group  by，再用having

3. 测试验证

-- 创建员工表
CREATE TABLE `tab_employee` (
  `id`     BIGINT(20) NOT NULL AUTO_INCREMENT,
  `num`    VARCHAR(2) DEFAULT NULL COMMENT '统计',
  `name`   VARCHAR(255) DEFAULT NULL COMMENT '姓名',
  `grade`  VARCHAR(30) DEFAULT NULL COMMENT '级别',
  `salary` VARCHAR(30) DEFAULT NULL COMMENT '薪资',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '员工信息表';
-- 查询表
SELECT * FROM tab_employee;
-- 删除表
DROP TABLE tab_employee;
-- 删除数据
DELETE FROM tab_employee; 

INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','张三','A',1500);
INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','张三','A',2000);
INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','张三','B',1500);
INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','李四','A',1500);
INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','李四','B',2000);
INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','王五','A',1500);
INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','赵六','C',1500);
INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','田七','D',1500);

-- 查询表
SELECT * FROM tab_employee;

-- AABA BACD
SELECT grade FROM tab_employee;

-- 按照姓名分组
SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME;
-- 按照级别分组
SELECT COUNT(num),grade FROM tab_employee GROUP BY grade;
-- 按照姓名与级别分组，把NAME,grade这列看做一个整体
SELECT COUNT(num),NAME,grade FROM tab_employee GROUP BY NAME,grade; 
-- 按照级别与姓名分组，把grade,NAME这列看做一个整体,与上边区别是分组字段顺序颠倒
SELECT COUNT(num),NAME,grade FROM tab_employee GROUP BY grade,NAME; 

-- 配合聚合函数一起使用
-- 常用的聚合函数：count() , sum() , avg() , max() , min()

-- count()：计数，查看表中相同人名的个数
SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME;           

-- sum()：求和, 查看表中人员的工资和（同姓的工资相加）
SELECT SUM(salary),NAME FROM tab_employee GROUP BY NAME;  

-- avg()：平均数, 查看表中人员的工资平均数（同姓工资平均数）
SELECT NAME,AVG(salary) FROM tab_employee GROUP BY NAME,grade;
-- 姓名与级别确定一个人，所以会有两个张三都是1500
SELECT NAME,grade,AVG(salary) FROM tab_employee GROUP BY NAME,grade;

-- max()：最大值, 查看按等级划分人员工资最大值
SELECT grade, MAX(salary) FROM tab_employee GROUP BY grade;
SELECT NAME,grade, MAX(salary) FROM tab_employee GROUP BY grade,NAME;

-- min()：最小值, 查看按等级划分人员工资最小值
SELECT NAME,grade, MIN(salary) FROM tab_employee GROUP BY grade, NAME;

-- having相当于条件
SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME;  
SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME HAVING COUNT(num) > 1;