SQL 分组查询 -简述及举例
1、标准书写样式 （只能按照循序写）
SELECT
....
FROM
....
WHERE （条件）
....
GROUP BY （分组）
....
HAVING （筛选）
....
ORDER BY （排序）
....
2、sql执行循序
①、FROM
②、WHERE
③、GROUP BY
④、HAVING
⑤、SELECT
⑥、ORDER BY
3、注意事项：
sql语句中能用WHERE或者HAVING都能达到结果的，要用尽量WHERE；
在使用链接表时能用JOIN链接要用JOIN链接
HAVING 必须跟在分组函数后
--例子 --1、查询班级下的年龄大于20岁的学生 -- -- sql语句中能用WHERE或者HAVING都能达到结果的，要用尽量WHERE； SELECT * FROM `user` WHERE age>20 GROUP BY classid SELECT * FROM `user` GROUP BY classid HAVING age>20 --2、查询年级中大于最小年级的学生 SELECT * FROM `user` u1 WHERE u1.age > (SELECT MIN(u2.age) FROM `user` u2) SELECT *FROM `user` u1 JOIN (SELECT MIN(nu.age) nage FROM `user` nu) u2 ON u1.age> u2.nage
4、例子
-- 查询每个班级下的平均年龄大于20岁的学生,并且班级不是3，倒叙排序 SELECT classid, AVG( age ) FROM `user` WHERE classid != 3 GROUP BY classid HAVING AVG( age ) > 20 ORDER BY age DESC
建表语句：
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字', `age` int(3) NULL DEFAULT NULL COMMENT '年龄', `grade` double(5, 2) NULL DEFAULT NULL COMMENT '分数', `classid` int(11) NULL DEFAULT NULL COMMENT '班级id', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, '张三', 35, 24.00, 3); INSERT INTO `user` VALUES (3, '赵六', 23, 23.82, 2); INSERT INTO `user` VALUES (5, '李小', 34, 23.16, 3); INSERT INTO `user` VALUES (6, '赵四', 45, 34.00, 2); INSERT INTO `user` VALUES (7, '小红', 12, 23.98, 1); INSERT INTO `user` VALUES (8, '赵六', 23, 99.90, 2); INSERT INTO `user` VALUES (9, 'DAXIE', 32, 34.00, 2); INSERT INTO `user` VALUES (10, 'xiaoxie', 67, 89.00, 3); SET FOREIGN_KEY_CHECKS = 1;
