group by 多个字段

网上查了很多好像说的都对,但是啰嗦,我看着都费劲

在现实的应用场景中:

如果想要统计每个班的男生/女生的数量就可以group by 班级id,性别

代码如下 

SELECT
class.`name`,
(case when students.sex=1 then '男' else '女' end) AS sex1,
COUNT(students.id)
FROM
students
LEFT JOIN class ON students.classId = class.id
GROUP BY class.id,students.sex;

具体重现:

1.班级表:

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `name` varchar(250) DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='班级表';


-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '一班');
INSERT INTO `class` VALUES ('2', '二班');

2.学生表:

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `name` varchar(50) DEFAULT NULL COMMENT '学习姓名',
  `age` int(3) DEFAULT NULL COMMENT '学生年龄',
  `sex` int(1) DEFAULT NULL,
  `classId` int(11) DEFAULT NULL COMMENT '所属班级id',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  PRIMARY KEY (`id`),
  KEY `FK_Reference_1` (`classId`),
  CONSTRAINT `FK_Reference_1` FOREIGN KEY (`classId`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='学生信息表';


-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('姓名11', '15', '1', '1', '3');
INSERT INTO `students` VALUES ('姓名12', '16', '2', '1', '4');
INSERT INTO `students` VALUES ('姓名13', '15', '1', '1', '5');
INSERT INTO `students` VALUES ('姓名21', '15', '1', '2', '8');
INSERT INTO `students` VALUES ('姓名22', '15', '2', '2', '9');
INSERT INTO `students` VALUES ('姓名23', '15', '2', '2', '10');

3.查询:

SELECT
class.`name`,
(case when students.sex=1 then '男' else '女' end) AS sex1,
COUNT(students.id)
FROM
students
LEFT JOIN class ON students.classId = class.id
GROUP BY class.id,students.sex;