SQL语句中group by的用法

一、准备数据库

DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dept` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `salary` int(10) NULL DEFAULT NULL,
  `edlevel` int(10) NULL DEFAULT NULL,
  `hiredate` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of staff
-- ----------------------------
INSERT INTO `staff` VALUES (1, '张三', '开发部', 2000, 3, '2019-09-25 18:38:25');
INSERT INTO `staff` VALUES (2, '李四', '开发部', 2500, 3, '2009-10-01 00:00:00');
INSERT INTO `staff` VALUES (3, '王五', '设计部', 2600, 5, '2010-10-02 00:00:00');
INSERT INTO `staff` VALUES (4, '王六', '设计部', 2300, 4, '2010-10-03 00:00:00');
INSERT INTO `staff` VALUES (5, '马七', '设计部', 2100, 4, '2010-10-06 00:00:00');
INSERT INTO `staff` VALUES (6, '赵八', '销售部', 3000, 5, '2010-10-05 00:00:00');
INSERT INTO `staff` VALUES (7, '钱九', '销售部', 3100, 7, '2010-10-07 00:00:00');
INSERT INTO `staff` VALUES (8, '孙十', '销售部', 3500, 7, '2010-10-06 00:00:00');

SET FOREIGN_KEY_CHECKS = 1;

二、SQL语句

-- GROUP BY
1、必须在group之前限定where子句
2、在select语句中指定的每个列名也在group by子句中提到，未在这两个地方提到的列名会产生异常；
3、group by 列名1,列名2 返回的是2个列的唯一的组合各返回一行
		1 2
		1 3
		2 3
		2 4
		3 1
4、where和聚合函数不可以连用，比如 where count(*)>10是不正确的
但是having可以，having count(*)>10 是成立的。
having是在group by之后执行，而where是在group by之前执行的。


CREATE TABLE staff
(
	id int(10) PRIMARY key auto_increment,
	name varchar(255),
  dept varchar(255),
  salary int(10),
	edlevel int(10),
	hiredate datetime 
)

-- 查询每个部门的最高薪水
select MAX(salary) as 薪资
from 
staff
group by dept

-- 查询公司2010年入职的各个部门每个级别里的最高薪水
SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
FROM staff
WHERE HIREDATE > '2010-01-01'
GROUP BY DEPT, EDLEVEL
ORDER BY  EDLEVEL

-- 寻找雇员数超过2个的部门的最高和最低薪水
-- 按部门分组之后统计每个组的数量，所以会使用到聚合函数，所以，使用having 后面加聚合函数
SELECT dept as 部门,MAX(SALARY) as 最高薪水, MIN(SALARY) as 最低薪水
from staff
group by dept
having count(dept)>2

-- 寻找雇员平均工资大于3000的部门的最高和最低薪水：
-- 先根据部门进行分组，得到部门的平均工资
SELECT dept as 部门,MAX(SALARY) as 最高薪水, MIN(SALARY) as 最低薪水
from staff
group by dept
having AVG(SALARY)>3000