需求描述
要展示学生有哪些学科,多个学科用逗号分隔,可以按学科来进行筛选。
学生姓名 | 学科 |
---|---|
张三 | 数学,英语,语文 |
李四 | 毛概,政治 |
王五 | 毛概,政治 |
法外狂徒 | 计算机网络与原理 |
铁柱 | 计算机网络与原理,英语 |
表设计
学生表
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
学生姓名 | 学科 |
---|---|
张三 | 语文,数学,英语 |
李四 | 语文,数学 |
如果有更好的方法,请在下发留言!