mysql - MySQL在一次连接中从表中获得两行

我有三张桌子：

Trip
|  id  |   name  |
__________________
|  2   |    X    |
|  3   |    Y    |
|  4   |    Z    |

Tourist
| id   |   name  |
__________________
|  1   |   John  |
|  2   |    Doe  |
|  3   |  Margot |
|  4   |  Sarah  |
|  5   |   Bill  |
|  6   |   Alan  |

Reservation
| trip_id  |  tourist_id |
_________________________
|    2     |      1     |
|    2     |      4     |
|    4     |      6     |

我想以一种能让所有游客都使用一个旅行名称的方式加入这些表格：

{
    "trip_name": X,
    "tourists": [
        {
            "id": 1,
            "name": John
        },
        {
            "id": 4,
            "name": Sarah
        }
    ],
},
{
    "trip_name": Z,
    "tourists": [
        {
            "id": 6,
            "name": Alan
        }
    ],
}

这在一个查询中可能吗？还是应该进行多个查询？或者我应该只执行一个查询，然后循环遍历结果，然后按照我想要的方式创建对象？

最佳答案

试试这个

SELECT
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'trip_name',
            NAME,
            'tourists',
        (SELECT
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id',
                    tourist.id,
                    'name',
                    tourist.name
                )
            )
        FROM
            tourist,
            reservation
        WHERE
            reservation.tourist_id = tourist.id AND reservation.trip_id = trip.id
            )
        )
    )
FROM
    trip

db-fiddlehttps://www.db-fiddle.com/f/fbfCwrufpBLFRSYBmGaVjp/0

关于mysql - MySQL在一次连接中从表中获得两行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58881781/