select查询从主表中获取数据和子表中的所有值？

Question

我的表结构为：

Student: 
    ID Name   From 
    1 student A England 
    2 student B China 
    3 student C USA 

    Subject: 
    ID id_student Subject 
    1  1   Maths 
    2  1   Physics 
    3  2   English 
    4  3   Physics 
    5  4   History 

我想在主表（A）的所有数据，并在所有行都具有ID_A在子表（B）中网格表像这样显示：

ID Student   Subject 
1  student_A  Maths, Physics 
2  student_B  English 
3  student_C  Physics, History 

我不知道如何选择数据？

Answer 1

您可以使用GROUP_CONCAT：

SELECT t1.ID, t1.Name, GROUP_CONCAT(t2.Subject) 
FROM Student AS t1 
LEFT JOIN Subject AS t2 ON t1.ID = t2.id_student 
GROUP BY t1.ID, t1.Name