mysql分组查询GROUP_CONCAT 筛选数据

平常写sql遇到的一个问题，记录一下

有三张表student（学生表）、teacher（教师表）、stu_teacher（学生—教师关系表）

表结构及数据如下

 

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '张三');
INSERT INTO `student` VALUES ('2', '李四');
INSERT INTO `student` VALUES ('3', '王五');
INSERT INTO `student` VALUES ('4', '赵六');

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES ('1', '语文教师');
INSERT INTO `teacher` VALUES ('2', '数学教师');
INSERT INTO `teacher` VALUES ('3', '英语教师');
INSERT INTO `teacher` VALUES ('4', '体育教师');
INSERT INTO `teacher` VALUES ('5', '音乐教师');


CREATE TABLE `stu_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  `flag` int(11) DEFAULT '0' COMMENT '0-有效  1-无效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

INSERT INTO `stu_teacher` VALUES ('1', '1', '1', '0');
INSERT INTO `stu_teacher` VALUES ('2', '1', '2', '0');
INSERT INTO `stu_teacher` VALUES ('3', '1', '3', '0');
INSERT INTO `stu_teacher` VALUES ('4', '2', '2', '0');
INSERT INTO `stu_teacher` VALUES ('5', '2', '3', '0');
INSERT INTO `stu_teacher` VALUES ('6', '2', '4', '0');
INSERT INTO `stu_teacher` VALUES ('7', '2', '5', '0');
INSERT INTO `stu_teacher` VALUES ('8', '3', '1', '0');
INSERT INTO `stu_teacher` VALUES ('9', '3', '2', '0');
INSERT INTO `stu_teacher` VALUES ('10', '3', '3', '0');
INSERT INTO `stu_teacher` VALUES ('11', '3', '4', '0');
INSERT INTO `stu_teacher` VALUES ('12', '3', '5', '0');
INSERT INTO `stu_teacher` VALUES ('13', '4', '1', '1');

现在需要根据学生分组查询出每个学生有哪些教师，每个学生一条数据，要求每个学生都能展示出来

一开始sql为

SELECT
	s.`name` stuName,
	GROUP_CONCAT(t.`name` SEPARATOR ',') as teacherName
FROM
	student s
LEFT JOIN stu_teacher st ON s.id = st.stu_id
LEFT JOIN teacher t ON st.teacher_id = t.id
GROUP BY
	s.id

结果如下

后来业务要求只取关系表有效的数据，同时每个学生必须展示

一开始修改sql为

SELECT
	s.`name` stuName,
	GROUP_CONCAT(t.`name` SEPARATOR ',') as teacherName
FROM
	student s
LEFT JOIN stu_teacher st ON s.id = st.stu_id
LEFT JOIN teacher t ON st.teacher_id = t.id
where st.flag = 0
GROUP BY
	s.id

查询结果为：

结果赵六不见了。。。。。

后来查到GROUP_CONCAT方法可以使用case when 函数，于是修改sql为

SELECT
	s.`name` stuName,
	GROUP_CONCAT(
		(
			CASE st.flag
			WHEN 0 THEN
				t.`name`
			ELSE
				NULL
			END
		) SEPARATOR ','
	) AS teacherName
FROM
	student s
LEFT JOIN stu_teacher st ON s.id = st.stu_id
LEFT JOIN teacher t ON st.teacher_id = t.id
GROUP BY
	s.id

查询结果如下

满足需求