查询sql

SELECT s.student_id, s.name, c.name as course_name FROM student s
INNER JOIN course c ON FIND_IN_SET(c.course_id, s.course_id) > 0
WHERE 1 = 1;

相似sql

-- 翻译（需要带条件，可用于字典翻译，但条件需要注意唯一性）
SELECT s.student_id,s.name, GROUP_CONCAT(c.`name`) as course_name FROM student s
LEFT JOIN course c ON FIND_IN_SET(c.course_id, s.course_id) > 0
WHERE s.student_id = '20150101001';

测试数据

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (`student_id` varchar(20) NOT NULL COMMENT '主键',`name` varchar(300) DEFAULT NULL COMMENT '名称',`age` varchar(3) DEFAULT NULL COMMENT '年龄',`sex` varchar(1) DEFAULT NULL COMMENT '性别',`grade` varchar(300) DEFAULT NULL COMMENT '年级',`course_id` varchar(300) DEFAULT NULL COMMENT '课程ID',PRIMARY KEY (`student_id`)
) COMMENT='学生表';INSERT INTO student (student_id, name, age, sex, grade, course_id) VALUES('20130101001', '王五', '18', '女', 'G3', 'G3001,G3002,G3003,G3004,G3006,G3008');
INSERT INTO student (student_id, name, age, sex, grade, course_id) VALUES('20130101002', '孙七', '18', '女', 'G3', 'G3001,G3002,G3003,G3007,G3009,G3009');
INSERT INTO student (student_id, name, age, sex, grade, course_id) VALUES('20140101001', '李四', '16', '男', 'G2', 'G2001,G2002,G2003,G2004,G2007,G2008');
INSERT INTO student (student_id, name, age, sex, grade, course_id) VALUES('20140101002', '钱二', '16', '男', 'G2', 'G2001,G2002,G2003,G2004,G2005,G2007');
INSERT INTO student (student_id, name, age, sex, grade, course_id) VALUES('20150101001', '张三', '15', '男', 'G1', 'G1001,G1002,G1003,G1004,G1005,G1006');
INSERT INTO student (student_id, name, age, sex, grade, course_id) VALUES('20150101002', '赵一', '15', '男', 'G1', 'G1001,G1002,G1003,G1006,G1008,G1008');DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (`course_id` varchar(20) NOT NULL COMMENT '主键',`name` varchar(300) DEFAULT NULL COMMENT '课程名称',`duration` varchar(300) DEFAULT NULL COMMENT '课程时长',`grade` varchar(300) DEFAULT NULL COMMENT '年级',PRIMARY KEY (`course_id`)
) COMMENT='课程表';INSERT INTO course (course_id, name, duration, grade) VALUES('G1001', '语文', '35', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1002', '数学', '40', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1003', '英语', '32', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1004', '生物', '35', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1005', '物理', '40', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1006', '化学', '32', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1007', '历史', '35', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1008', '政治', '40', '高1');
INSERT INTO course (course_id, name, duration, grade) VALUES('G1009', '地理', '32', '高1');INSERT INTO course (course_id, name, duration, grade) VALUES('G2001', '语文', '35', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2002', '数学', '40', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2003', '英语', '32', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2004', '生物', '35', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2005', '物理', '40', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2006', '化学', '32', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2007', '历史', '35', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2008', '政治', '40', '高2');
INSERT INTO course (course_id, name, duration, grade) VALUES('G2009', '地理', '32', '高2');INSERT INTO course (course_id, name, duration, grade) VALUES('G3001', '语文', '35', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3002', '数学', '40', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3003', '英语', '32', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3004', '生物', '35', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3005', '物理', '40', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3006', '化学', '32', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3007', '历史', '35', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3008', '政治', '40', '高3');
INSERT INTO course (course_id, name, duration, grade) VALUES('G3009', '地理', '32', '高3');

效果图1

效果图2