GROUP BY :分组查询,语句用于结合聚合函数,根据一个或多个列对结果集进行分组,最终得到一个分组汇总表。
一个学生信息表结构如下:
id name age teacher class ranking school
1 sunyan 22 a 1 3 x
2 liukan 21 a 3 2 x
3 zhangkai 19 b 1 1 x
4 liuyongting 20 b 1 4 y
5 zhanbin 24 b 3 5 y
6 wangxiao 22 a 3 6 y
2 liukan 21 a 3 2 x
3 zhangkai 19 b 1 1 x
4 liuyongting 20 b 1 4 y
5 zhanbin 24 b 3 5 y
6 wangxiao 22 a 3 6 y
sql语句(拿去直接用)
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(64) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(64) DEFAULT NULL,
`teacher` varchar(255) DEFAULT NULL,
`class` varchar(255) DEFAULT NULL,
`ranking` varchar(255) DEFAULT NULL,
`school` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(64) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(64) DEFAULT NULL,
`teacher` varchar(255) DEFAULT NULL,
`class` varchar(255) DEFAULT NULL,
`ranking` varchar(255) DEFAULT NULL,
`school` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'sunyan', '22', 'a', '1', '3', 'x');
INSERT INTO `student` VALUES ('2', 'liukan', '21', 'a', '3', '2', 'x');
INSERT INTO `student` VALUES ('3', 'zhangkai', '19', 'b', '1', '1', 'x');
INSERT INTO `student` VALUES ('4', 'liuyongting', '20', 'b', '1', '4', 'y');
INSERT INTO `student` VALUES ('5', 'zhanbin', '24', 'b', '3', '5', 'y');
INSERT INTO `student` VALUES ('6', 'wangxiao', '22', 'a', '3', '6', 'y');
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'sunyan', '22', 'a', '1', '3', 'x');
INSERT INTO `student` VALUES ('2', 'liukan', '21', 'a', '3', '2', 'x');
INSERT INTO `student` VALUES ('3', 'zhangkai', '19', 'b', '1', '1', 'x');
INSERT INTO `student` VALUES ('4', 'liuyongting', '20', 'b', '1', '4', 'y');
INSERT INTO `student` VALUES ('5', 'zhanbin', '24', 'b', '3', '5', 'y');
INSERT INTO `student` VALUES ('6', 'wangxiao', '22', 'a', '3', '6', 'y');
应用
查询每个班年龄最大的学生的姓名,班级,和年龄
SELECT name ,class ,MAX(age) AS MAXAGE FROM student
GROUP BY class;
查询结果
name class MAXAGE
sunyan 1 22
liukan 3 24
结果解释:
select 查询这个结果,MAX(age)年龄最大值,AS重命名为 MAXAGE ,GROUP BY按班级分组。
将where 子句与 GROUP BY一起使用
分组查询可以在形成组和计算列函数之前具有消除非限定行的标准where子句,必须在group by子句之前指定where子句!
查询x学校的每个班年龄最大的学生的姓名,班级,和年龄
SELECT name ,class ,MAX(age) AS MAXAGE FROM student
WHERE school='x'
GROUP BY class;
查询结果
name class MAXAGE
sunyan 1 22
liukan 3 21
GROUP BY子句之后使用Having子句
可应用限定条件进行分组,以便系统返回仅满足条件的组返回结果。因此GROUP BY子句后面包含一个HAVING。HAVING类似于WHERE (唯一的差别是WHERE过滤行,HAVING过滤组)HAVING支持所有WHERE操作符。SELECT name ,class ,MIN(ranking) AS MINRANKING FROM student
GROUP BY class
HAVING AVG(age)>20;
查询结果
name class MINRANKING
sunyan 1 1liukan 3 2
*AVG(age):取年龄的平均值。
name class MAXAGE
sunyan 1 22
liukan 3 21