MYSQL 一对多查询

一．原始表格

学生信息表 student

课程表 course

 

 

二．查询效果

１．多行展示

 

SELECT s.name, c.course 
FROM student  s
LEFT JOIN course c
ON s.id = c.stu_id 
WHERE s.`name` = "娜扎"

２．添加序号

SELECT (@i :=@i + 1) i, s.name, c.course 
FROM student  s
LEFT JOIN course c
ON s.id = c.stu_id ,
(select @i:=0) as it
WHERE s.`name` = "娜扎"

３．汇总展示

SELECT s.name ,GROUP_CONCAT(c.course SEPARATOR ',') as courses
FROM student s
LEFT JOIN course c
ON s.id =  c.stu_id
WHERE s.`name` = "娜扎"