mysql - 连接并使子表进入父表的新列

Question

我想知道这是否可能；为了能够拥有两个表，然后将它们连接起来，但在子表上具有名称(或我选择的任何字段)，请进入父表，但在新列中

假设我的表 1 是“站点”。它指定某些程序的站点。
表2是出席人数，记录了当天在现场的人数。

+--------+--------------------------+-------------+-------+-------+------------+
| p2p_id | address                  | city        | state | zip   | date       |
+--------+--------------------------+-------------+-------+-------+------------+
|    44  | 435 S                    | Los Angeles | CA    | 90048 | 2014-05-13 |
|    45  | 1641 whatever  Ave       | Santa Ana   | CA    | 92704 | 2014-05-16 |
|    46  | 1710 reterrr St          | Denver      | CO    | 80202 | 2014-07-10 |
|    47  | 6401 fdgdfdffffAve       | Raleigh     | NC    | 27617 | 2014-07-16 |
|    48  | East dfgdfgdf  Street    | San Antonio | TX    | 76107 | 2014-05-13 |
|    126 | 3100 fgdfgffgf           | Fort Worth  | TX    | 76107 | 2014-05-14 |
|    127 | 1001 dfgdfgdffff         | Houston     | TX    | 77002 | 2014-05-20 |
|    128 | 303 fdgdfgfgfgf          | Atlanta     | GA    | 30308 | 2014-05-22 |
|    129 | 2525 W End Ave           | Nashville   | TN    | 37203 | 2014-05-22 |
|    13  | 2041 S xzcdfdf           | Anaheim     | CA    | 92802 | 2014-05-28 |
+--------+--------------------------+-------------+-------+-------+------------+

然后我们有“出席”表或表 2

+------------+-------------+--------+
| first_name | last_name   | p2p_id |
+------------+-------------+--------+
    |bara    | Edgar       | 44
    |        | Estelle     | 44
    |chi     | NG          | 44
    |nhar    | Poon        | 44
    |ie      | Byrd        | 48
    |nie     | Gilet       | 48
    |nie     | Hawley      | 48
    |helle   | Hewlett     | 48
    |orah    | Siler       | 48
    |hy      | Sommerville | 48
+------------+-------------+--------+

p2p_id 是“Site”表上的主键，p2p_id 是“Attend”表上的外键。

问题是我可以加入

SELECT * FROM site s 
JOIN attend a
ON s.p2p_id=a.p2p_id


+--------+--------------------------+-------------+-------+-------+------------+ ------------+-------------+--------+
| p2p_id | address                  | city        | state | zip   | date       |  first_name | last_name   | p2p_id |
+--------+--------------------------+-------------+-------+-------+------------+ ------------+-------------+--------+
|    44  | 435 S                    | Los Angeles | CA    | 90048 | 2014-05-13 |     bara    | Edgar       | 44
|    44  | 435 S                    | Los Angeles | CA    | 90048 | 2014-05-13 |             | Estelle     | 44
|    44  | 435 S                    | Los Angeles | CA    | 90048 | 2014-05-13 |     chi     | NG          | 44 
|    44  | 435 S                    | Los Angeles | CA    | 90048 | 2014-05-13 |     nhar    | Poon        | 44
|    44  | 435 S                    | Los Angeles | CA    | 90048 | 2014-05-13 |      ie     | Byrd        | 48
|    48  | East dfgdfgdf  Street    | San Antonio | TX    | 76107 | 2014-05-13 |     nie     | Gilet       | 48
|    48  | East dfgdfgdf  Street    | San Antonio | TX    | 76107 | 2014-05-13 |     helle   | Hewlett     | 48
|    48  | East dfgdfgdf  Street    | San Antonio | TX    | 76107 | 2014-05-13 |     orah    | Siler       | 48
|    48  | East dfgdfgdf  Street    | San Antonio | TX    | 76107 | 2014-05-13 |     hy      | Sommerville | 48

但是，SQL(MySQL)是否可以将其设置为这种格式，而不是弹出同一站点的多行？

+--------+--------------------------+-------------+-------+-------+------------+ ------------+-------------+ ------------+-------------+------------+-------------+
| p2p_id | address                  | city        | state | zip   | date       |  first_name | last_name   |  first_name | last_name   | first_name | last_name   |
+--------+--------------------------+-------------+-------+-------+------------+ ------------+-------------+ ------------+-------------+------------+-------------+
|    44  | 435 S                    | Los Angeles | CA    | 90048 | 2014-05-13 |     Barb    | Edgar       |             | Estelle     |    Chi     | Ngu         |
|    48  | East dfgdfgdf  Street    | San Antonio | TX    | 76107 | 2014-05-13 |     Helle   | Hewlett     |     Sarah   | Siler       |    Barbara | Walters     |

如果您想知道为什么我不直接在父表中使用这些名称创建新列...好吧，我不想以这种方式存储我的数据，但我公司的其他一些人需要我这样做以该格式导出数据。

谢谢!!

Answer 1

据我所知，如果没有一些疯狂的临时表，这是不可能的。我认为您能做的最好的事情就是在使用 group_concat 函数后通过一些编程来处理结果，如下所示:

SELECT a.p2p_id, a.address,a.city, a.state,a.zip, a.date, group_concat(concat(first_name,' ', last_name)) as name
FROM site s 
JOIN attend a
ON s.p2p_id=a.p2p_id 
group by a.p2p_id, a.address,a.city, a.state,a.zip, a.date,