MySQL查询条件中使用GROUP_CONCAT函数中的列查询问题

需求描述

要展示学生有哪些学科，多个学科用逗号分隔，可以按学科来进行筛选。

学生姓名	学科
张三	数学,英语,语文
李四	毛概,政治
王五	毛概,政治
法外狂徒	计算机网络与原理
铁柱	计算机网络与原理,英语

表设计

学生表

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `t_student` VALUES (1, '张三');
INSERT INTO `t_student` VALUES (2, '李四');
INSERT INTO `t_student` VALUES (3, '王五');
INSERT INTO `t_student` VALUES (4, '法外狂徒');
INSERT INTO `t_student` VALUES (5, '铁柱');
COMMIT;

学科表

CREATE TABLE `t_subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(10) DEFAULT NULL COMMENT '学科',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `t_subject` VALUES (1, '语文');
INSERT INTO `t_subject` VALUES (2, '数学');
INSERT INTO `t_subject` VALUES (3, '英语');
INSERT INTO `t_subject` VALUES (4, '政治');
INSERT INTO `t_subject` VALUES (5, '毛概');
INSERT INTO `t_subject` VALUES (6, '计算机网络与原理');
COMMIT;

学生学科关联关系表

CREATE TABLE `t_stu_sub_relationship` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL COMMENT '学生ID',
  `subject_id` int(11) DEFAULT NULL COMMENT '学科ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `t_stu_sub_relationship` VALUES (1, 1, 1);
INSERT INTO `t_stu_sub_relationship` VALUES (2, 1, 2);
INSERT INTO `t_stu_sub_relationship` VALUES (3, 1, 3);
INSERT INTO `t_stu_sub_relationship` VALUES (4, 2, 1);
INSERT INTO `t_stu_sub_relationship` VALUES (5, 2, 2);
INSERT INTO `t_stu_sub_relationship` VALUES (6, 3, 4);
INSERT INTO `t_stu_sub_relationship` VALUES (7, 3, 5);
INSERT INTO `t_stu_sub_relationship` VALUES (8, 4, 6);
INSERT INTO `t_stu_sub_relationship` VALUES (9, 5, 3);
INSERT INTO `t_stu_sub_relationship` VALUES (10, 5, 6);
COMMIT;

查询

查询全部学生学科

SELECT st.`name`, GROUP_CONCAT(sb.`subject` ORDER BY sb.id)
FROM t_student st
LEFT JOIN t_stu_sub_relationship r ON st.id = r.student_id
LEFT JOIN t_subject sb ON r.subject_id = sb.id
GROUP BY st.id

学生姓名	学科
张三	数学,英语,语文
李四	数学,语文
王五	毛概,政治
法外狂徒	计算机网络与原理
铁柱	计算机网络与原理,英语

按学科=语文查询

SELECT st.`name`, GROUP_CONCAT(sb.`subject` ORDER BY sb.id)
FROM t_student st
LEFT JOIN t_stu_sub_relationship r ON st.id = r.student_id
LEFT JOIN t_subject sb ON r.subject_id = sb.id
WHERE r.subject_id = 2
GROUP BY st.id

学生姓名	学科
张三	语文
李四	语文

按学科查询就会出现以上问题！学科字段只显示了语文，正常还有其他的学科没有显示！

解决

SELECT st.`name`, GROUP_CONCAT(DISTINCT sb.`subject` ORDER BY sb.id)
FROM t_student st
LEFT JOIN t_stu_sub_relationship r ON st.id = r.student_id
LEFT JOIN t_stu_sub_relationship r2 ON st.id = r2.student_id
LEFT JOIN t_subject sb ON r.subject_id = sb.id
WHERE r2.subject_id = 2
GROUP BY st.id

学生姓名	学科
张三	语文,数学,英语
李四	语文,数学

如果有更好的方法，请在下发留言！