联接两个表列 - Join two table columns

Join two table columns

本文关键字：两个 | 更新日期: 2023-09-27

我有两个表格，如下所示：

团队

teamid   team_name
  1      Rockstars
  2      Gangsters

team_members

teamid    team_member_name
  1         Rob
  1         Mike
  2         John
  2         Paul

我想得到这样的结果：

teamid      team_members    team_name
   1        Rob, Mike       Rockstars
   2        John, Paul      Gangsters

我试过这个：（不起作用）

Select teamid, CONCAT(select team_member_name FROM team_members WHERE teams.teamid = team_members.teamid) as team_members FROM teams;

请帮忙

你需要的是GROUP_CONCAT：

SELECT t1.teamid, t1.team_name, GROUP_CONCAT(t2.team_member_name)
FROM teams AS t1
INNER JOIN team_members AS t2 ON t1.teamid = t2.teamid
GROUP BY t1.teamid, t1.team_name

在这里演示