mysql - 连接 MySQL 表上的多个列

Question

我正在尝试连接 MySQL 表上的多个列。我不知道如何将多列检索到一行中，以便我可以在页面上显示一行。

表:employee_timesheet

id    employee_id    service_area_name    sign_off_1    sign_off_2  sign_off_3    created_date           status       last_updated
1     2              London               Rom           Director    NULL          2015-02-11 17:22:44    Submitted    2015-02-11 17:22:44

表(空):employees_timesheet_sign_off_team_leaders

id    employee_id    timesheet_id    sign_off_key    team_leader_id

表:employees_timesheet_sign_off_roms

id    employee_id    timesheet_id    rom_id    sign_off_key
1     2              1               4         sign_off_1
2     2              1               5         sign_off_1

表格:team_leaders

id    first_name    last_name    email                  reports_to    status    date_added              last_updated
2     Bob           Test         me@mydomain.com        4             Active    2015-02-03 12:50:25     2015-02-03 13:28:56

表:ROM

id    first_name    last_name    email                          status    date_added              last_updated
4     Bill          Gates        bill@mydomain123445678.com     Active    2015-02-03 13:14:07     2015-02-03 13:28:40
5     Ben           Morris       ben@mydomain123445678.co.uk    Active    2015-02-11 17:35:43     NULL

我需要连接上面的表才能得到以下结果:

ID: 1
Team Leader: (null)
Rom: Bill Gates,Ben Morris
Date: 2015-02-11 17:22:44

如果有人能提供帮助，我们将不胜感激。

提前致谢。

Answer 1

对于您的用例，您需要使用 GROUP_CONCAT 函数:http://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/ 。查询看起来像这样:

SELECT et.id, CONCAT(l.first_name, ' ', l.last_name) 'team_leader', GROUP_CONCAT(DISTINCT CONCAT(r.first_name, ' ', r.last_name)), et.last_updated
FROM employee_timesheet et
INNER JOIN employees_timesheet_sign_off_roms etr ON et.id = etr.timesheet_id
INNER JOIN roms r ON etr.rom_id = r.id
LEFT JOIN employees_timesheet_sign_off_team_leaders etl ON et.id = etl.timesheet_id
LEFT JOIN team_leaders l ON etl.team_leader_id = l.id
GROUP BY et.id, team_leader, et.last_updated