mysql分组查询GROUP_CONCAT 筛选数据

偏执的太偏执、 2022-03-12 19:22 183阅读 0赞

平常写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

结果如下

![20190306160745130.png][]

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

一开始修改sql为

查询结果为：

![20190306161017332.png][]

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

后来查到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

查询结果如下

![20190306161232531.png][]

满足需求

[20190306160745130.png]: /images/20220312/d7d48851143849bba8987c7c818f4b89.png
[20190306161017332.png]: /images/20220312/3756a8397d524d9ca9a6558ca5e5c973.png
[20190306161232531.png]: /images/20220312/4aca63d1962943509c58c77fded46074.png