mysql - 合并两个表mysql

Question

是否可以像这样组合这两个表？表 2 中的所有主题应与表 1 合并，并且主题应与表 1 中的相同 ID 对齐在 1 行中。

Table1 
----------------------
ID   Student_ID   Name

 1   200000      Paul

 2   202907      Ger


Table2
----------------------
Student_ID   Subject

200000       ACT111

200000       ACT112

200000       ACT113

202907       ACT111

-------------------


Expected Combination
------------------------------------------------------
ID       Student_ID     Name            Subject
1        200000         Paul      ACT111 ACT112 ACT113  

Answer 1

您必须关联表才能获取数据(请参阅 JOIN syntax )，然后您可以使用 group_concat()获取以逗号分隔的值列表:

select t1.id, t1.student_id, t1.name
     , group_concat(t2.subject separator ',') as subjects
from table1 as t1
     inner join table2 as t2 on t1.student_id = t2.student_id
--   ^^^^^^^^^^ Replace 'INNER JOIN' with 'LEFT JOIN' if you want to
--              also show the students with no subjects
group by t1.id;