[TOC] ### GROUP 分组查询 >[danger] 分组查询，顾名思义就是按照组区分开，进行查询，比如按照性别分组 > 注意！！！ group by 后面分组的字段不能随便加，比如 group by 'sex' 那么，查询字段必须为 sex ### 按照性别分组查询全部数学成绩的平均分 ~~~ const { Sequelize } = app; // 按照性别分组查询全班数学成绩的平均分 const ret = await Student.findAll({ // 查询字段要和分组字段一致！！！！ attributes:['sex', [ Sequelize.fn('AVG', Sequelize.col('math')), 'math_avg' ] ], // 分组字段 group: ['sex'] }) SELECT `sex`, AVG(`math`) FROM `student` GROUP BY `sex`; [ { "sex": "女", "math_avg": "85.4286" }, { "sex": "男", "math_avg": "78.0000" } ] ~~~ ***** ### 按照性别分组，分别查询全班同学数学成绩平均分和总人数 ~~~ // 按照性别分组，分别查询全班同学数学成绩平均分和总人数 const ret = await Student.findAll( { attributes:[ 'sex', [Sequelize.fn('AVG', Sequelize.col('math')), 'match_avg'], [Sequelize.fn('COUNT', Sequelize.col('id')), 'total_student'] ], group: ['sex'] } ) SELECT `sex`, AVG(`math`) , COUNT(`id`) FROM `student` GROUP BY `sex`; [ { "sex": "女", "match_avg": "85.4286", "total_student": 7 }, { "sex": "男", "match_avg": "78.0000", "total_student": 5 } ] ~~~ ***** ### 按照地区分组，分别统计每个地区数学成绩平均分 和 总人数，分数低于70分的同学不参与分组 ~~~ // 按照地区分组，分别统计每个地区数学成绩平均分 和 总人数，分数低于70分的同学不参与分组 const ret = await Student.findAll( { attributes:[ 'address', [ Sequelize.fn('AVG', Sequelize.col('math')), 'math_avg' ], [ Sequelize.fn('COUNT', Sequelize.col('id')), 'total_student' ] ], where: { math: { [Op.gt]:70 } }, group: ['address'] } ) SELECT `address`, AVG(`math`) , COUNT(`id`) FROM `student` WHERE `math` > 70 GROUP BY `address`; [ { "address": "四川", "math_avg": "84.0000", "total_student": 2 }, { "address": "昆明", "math_avg": "90.0000", "total_student": 1 }, { "address": "潭州", "math_avg": "92.0000", "total_student": 1 }, { "address": "西凉", "math_avg": "82.0000", "total_student": 2 }, { "address": "贵州", "math_avg": "86.2500", "total_student": 4 } ] ~~~ ***** ### 按照地区分组，分别统计每个地区学生的数学平均分，分数低于70分的同学不参与分组，并且参与分组的人数不能少于2人 >[danger] 注意！！！where 和 having 的区别？ > where 在分组之前进行限定，如果不满足条件，则不参与分组 > having 在分组之后进行限定，如果不满足结果，则不会被查询出来 > where 后不可以带上聚合函数判断，having 后可以带上聚合函数进行判断 ~~~ await Student.findAll( { attributes:[ 'address', [Sequelize.fn('AVG', Sequelize.col('math')), 'math'], [Sequelize.fn('COUNT', Sequelize.col('id')), 'total'] ], where: { math: { [Op.gt]:70 } }, group: 'address', having: { total:{ [Op.gt]:2 } } }) SELECT `address`, AVG(`math`),COUNT(`id`) as total FROM `student` WHERE `student` > 70 GROUP BY `address` HAVING `total` > 2; ~~~