INNER或LEFT将多个表记录连接成一行 [英] INNER or LEFT Joining Multiple Table Records Into A Single Row

问题描述

电话表

+----------------+-------------+
| 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联接，然后将这些结果合并为一行?我已经看到过类似的问题，但是给出的示例比我所追求的要复杂得多:

Is it possible to do a LEFT or INNER join and then merge those results into a single row? I've seen similiar questions on this, but the examples given were much more complex than what I'm after:

重要问题

• MySQL需要帮助来构造查询:join将多个表排成一行
• SQL左连接，将多行合并为一行

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

谢谢

推荐答案

尽管您可以将多个数字(任意)连接到一个字段中

Though you can join several numbers (in any) into a single field:

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;

这篇关于INNER或LEFT将多个表记录连接成一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！