mysql 字段多个值，对应其他表的值，显示在一行怎么实现？

Question

select loginid,course from student

| loginid| course  |
| xy001| 1,2,3 |
| xy002| 1,3,5 |
| xy003| 1,2,5 |

select id,course from course

| id | course  |
| 1  | 语文 |
| 2  | 数学 |
| 3  | 地理 |

想要的效果

| loginid| course  |
| xy001| 语文,数学,地理 |
| xy002| 语文,地理,体育 |
| xy003| 语文,数学,化学 |

sql 怎么写

Answer 1

把开头的dev改成你的schema名字

USE `dev`;
DROP procedure IF EXISTS `abc`;

DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table AS SELECT * FROM student;

DELIMITER $$
CREATE PROCEDURE `abc`()
begin
DECLARE i,c int DEFAULT 0;
DECLARE notdone boolean DEFAULT True;
DECLARE sid VARCHAR(255);
DECLARE courseNums VARCHAR(255);
DECLARE courseN VARCHAR(255);

-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select loginid,course from tmp_table;

-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET notdone = False;

-- 打开游标
open report;

-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
FETCH report INTO sid,courseNums;

while notdone do
-- 执行业务逻辑
    set c= length(courseNums)-length(replace(courseNums,',',''));
    while i<c+1 do
        set courseN = substring_index(substring_index(courseNums,',',i+1),',',-1);
        set courseNums = replace(courseNums,courseN,(select course from course where id = courseN));
        update tmp_table set course = courseNums where loginid = sid;
        set i = i+1;
    end while;
-- 将游标中的值再赋值给变量，供下次循环使用
FETCH report INTO sid,courseNums;
set i =0;

end while;
-- 关闭游标
close report;
select * from tmp_table;
end$$

DELIMITER ;

CALL `abc`();

不知道你原来id是5的course是什么，这里是体育