当前位置：首页 > article >正文

MySql字段的值是以逗号隔开的另一个表的主键关联查询

article 2025/1/23 4:28:07

查询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');