在1(主表)对多(从表)关系中，假设是1对三的关系，如何通过sql把从表的记录拼接在主表记录后（不是列转行）

有没有大佬来帮下萌新，批评指点都huan'ying

-- 建表语句
/*

CREATE TABLE student (
sid int(15) DEFAULT NULL COMMENT '学生id',
sname varchar(255) DEFAULT NULL COMMENT '学生姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

CREATE TABLE course (
sid int(15) DEFAULT NULL COMMENT '学生id',
subject varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '课程',
teacher varchar(255) DEFAULT NULL COMMENT '老师'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

*/

-- 新增测试数据
/*

INSERT INTO test.student (sid, sname) VALUES ('1', '张三');
INSERT INTO test.student (sid, sname) VALUES ('2', '李四');
INSERT INTO test.student (sid, sname) VALUES ('3', '王五');

INSERT INTO test.course (sid, subject, teacher) VALUES ('1', '英语', 'A');
INSERT INTO test.course (sid, subject, teacher) VALUES ('1', '语文', 'B');
INSERT INTO test.course (sid, subject, teacher) VALUES ('1', '数学', 'C');
INSERT INTO test.course (sid, subject, teacher) VALUES ('2', '历史', 'A');
INSERT INTO test.course (sid, subject, teacher) VALUES ('2', '化学', 'D');
INSERT INTO test.course (sid, subject, teacher) VALUES ('2', '物理', 'B');

*/

-- 以下是动态处理方法，适用于列不确定情况

-- EE主要用于拼接查询的表，即from开始的语句
SET @EE=' from student s1';

select @EE :=CONCAT(@EE,' left join (SELECT *, row_number() over(partition by sid) id FROM course) c',id,' on s1.sid = c',id,'.sid and c',id,'.id = ',id) AS aa
FROM (SELECT distinct row_number() over(partition by sid order by teacher) id FROM course) A ;

-- QQ主要用于拼接查询的字段
SET @QQ='select s1.*,';

select @QQ :=CONCAT(@QQ,'c',id,'.subject subject',id,',','c',id,'.teacher teacher',id,',') AS aa
FROM (SELECT distinct row_number() over(partition by sid order by teacher) id FROM course) A ;

-- 最后将QQ和EE拼接起来，连成完整的sql语句
SET @QQ = CONCAT(substr(@QQ,1,length(@QQ)-1),@EE);

-- 预处理
PREPARE stmt FROM @QQ;
-- 执行语句
EXECUTE stmt;
-- 执行后释放资源
DEALLOCATE PREPARE stmt;

你的结果里，第一列指的sid，第二列指的是姓名，但是你的第三列，第四列，第五列。。。都分别指什么呢？没有固定的结构怎么处理数据啊。

如果你的一对 n 关系是确定的话，可以用多个 left join。
假设你的主表叫 tblMain，子表叫 tblSub，做联结时需要添加一个辅助排序列 id

select t1.*, 
    t2.class class1, t2.teacher teacher1,
    t3.class class2, t3.teacher teacher2,
    t4.class class3, t4.teacher teacher3
from tblMain t1
left join (SELECT *, row_number() over(partition by sid order by teacher) id FROM tblSub) t2
on t1.sid =  t2.sid and t2.id = 1
left join (SELECT *, row_number() over(partition by sid order by teacher) id FROM tblSub) t3
on t1.sid =  t3.sid and t3.id = 2
left join (SELECT *, row_number() over(partition by sid order by teacher) id FROM tblSub) t4
on t1.sid =  t4.sid and t4.id = 3