mysql - INNER 或 LEFT 将多个表记录连接成一行

Question

电话表

+----------------+-------------+| Field          | Type        |+----------------+-------------+| f_id           | int(15)     || f_client_id    | int(11)     || f_phone_type   | varchar(50) || f_phone_number | varchar(13) |+----------------+-------------+

客户表

+-----------------------------+--------------+------+-----+| Field                       | Type         | Null | Key |+-----------------------------+--------------+------+-----+| f_id                        | int(15)      | NO   | PRI || f_first_name                | varchar(13)  | YES  | MUL || f_mi                        | char(1)      | YES  |     || f_last_name                 | varchar(20)  | NO   | MUL |+-----------------------------+--------------+------+-----+

假设:

1. “电话表”中的每条记录都属于“客户表”中的一条记录。
2. “客户表”中的每条记录在“电话表”中可以有 0 条或多条记录。
3. 简单翻译:一个客户可以有 0 个或多个电话号码

使用标准的 LEFT 或 INNER 连接，我得到如下结果:

+------------+------------+--------------+| name       | Phone Type | Phone Number |+------------+------------+--------------+| John Smith | Home       | 712-555-6987 || John Smith | Work       | 712-555-1236 |+------------+------------+--------------+

我需要一个查询来为我提供属于给定客户的工作和家庭号码:

+------------+----------------+--------------+| Name       | Work Number    | Home Number  |  +------------+----------------+--------------+| John Smith | 712-555-1236   | 712-555-6987 |+------------+----------------+--------------+

是否可以执行 LEFT 或 INNER 连接，然后将这些结果合并到一行中？我见过类似的问题，但给出的例子比我所追求的要复杂得多:

类似问题

• MySQL Need help constructing query: join multiple tables into single row
• SQL left join with multiple rows into one row

谢谢

Answer 1

虽然您可以将多个数字(任意)连接到一个字段中:

SELECT
  CONCAT(f_first_name, ' ', f_last_name) as Client_Name,
  GROUP_CONCAT(IF(phone_type='work',f_phone_number, NULL)) as Work_Numbers,
  GROUP_CONCAT(IF(phone_type='home',f_phone_number, NULL)) as Home_Numbers
FROM clients
JOIN phone
  USING (f_id)
WHERE phone_type IN ('home', 'work')
GROUP BY f_id;