mysql - Mysql JOIN 几行多行的表

问题描述

我的mysql语句有问题。我需要加入几行多行的表，我想收到类似的东西：

广告中的 1 行
来自 ad_city 的 4 行（ON ad.id = ad_city.adId）
ad_entry 中的 4 行 (ON ad.id = ad_entry.adId)
来自 ad_object 的 4 行 (ON ad.id = ad_object.adId)

SELECT
    a.id,
    a.date,
    a.text,
    u.id AS "userId",
    u.name AS "userName",
    u.surname AS "userSurname",
    te.id AS "entryId",
    tet.name AS "entryType",
    ter.name AS "entryRegion",
    to.id AS "objectId",
    tot.name AS "objectType",
    tor.name AS "objectRegion",
    c.id AS "cityId",
    c.name AS "city",
    ac.acId,
    ac.acAdId,
    ac.acCityId,
    ac.acCity,
    ae.aeId,
    ae.aeAdId,
    ae.aeEntryId,
    ae.aeEntryType,
    ae.aeEntryRegion,
    ao.aoId,
    ao.aoAdId,
    ao.aoObjectId,
    ao.aoObjectType,
    ao.aoObjectRegion,
FROM
    `ad` AS `a`
    LEFT JOIN (
        SELECT
            ac.id AS "acId",
            ac.ad_id AS "acAdId",
            c.id AS "acCityId",
            c.name AS "acCity",
        FROM
            `ad_city` AS `ac`
            LEFT JOIN `main_city` AS `c` ON ac.main_city_id = c.id
    ) AS `ac` ON ac.acAdId = a.id
    LEFT JOIN (
        SELECT
            ae.id AS "aeId",
            ae.ad_id AS "aeAdId",
            to.id AS "aeEntryId",
            tot.name AS "aeEntryType",
            tor.name AS "aeEntryRegion",
        FROM
            `ad_entry` AS `ae`
            LEFT JOIN `some_object` AS `to` ON ae.some_object_id = to.id
            LEFT JOIN `some_type` AS `tot` ON to.some_type_id = tot.id
            LEFT JOIN `some_region` AS `tor` ON to.some_region_id = tor.id
    ) AS `ae` ON ae.aeAdId = a.id
    LEFT JOIN (
        SELECT
            ao.id AS "aoId",
            ao.ad_id AS "aoAdId",
            to.id AS "aoObjectId",
            tot.name AS "aoObjectType",
            tor.name AS "aoObjectRegion",
        FROM
            `ad_object` AS `ao`
            LEFT JOIN `some_object` AS `to` ON ao.some_object_id = to.id
            LEFT JOIN `some_type` AS `tot` ON to.some_type_id = tot.id
            LEFT JOIN `some_region` AS `tor` ON to.some_region_id = tor.id
    ) AS `ao` ON ao.aoAdId = a.id
    LEFT JOIN `some_object` AS `to` ON a.some_object_id = to.id
    LEFT JOIN `some_type` AS `tot` ON to.some_type_id = tot.id
    LEFT JOIN `some_region` AS `tor` ON to.some_region_id = tor.id
    LEFT JOIN `some_object` AS `te` ON a.some_entry_id = te.id
    LEFT JOIN `some_type` AS `tet` ON te.some_type_id = tet.id
    LEFT JOIN `some_region` AS `ter` ON te.some_region_id = ter.id
    LEFT JOIN `main_city` AS `c` ON a.main_city_id = c.id
    LEFT JOIN `user` AS `u` ON a.user_id = u.id
WHERE
    a.id = 1 -- example
    AND a.user_id = 2 -- example
ORDER BY
    a.date DESC

是否可以在一个语句中接收此数据？

我收到了 64 行，我必须将其映射到我需要的格式。在我的声明中，我从 ad_city、ad_entry、ad_object 加入 col adId，因为我需要这些变量来创建接收到的正确服务器。是否有可能从数据库接收更小的数据？

DB 到服务器正在发送 64 行数据。是大包。在服务器上，我必须执行许多循环和条件来格式化数据。

还是我应该向 DB 发送 4 个独立请求？

服务器响应：

{
    id: //
    date: //
    text: //
    user: {
        //
    },
    city: //
    entry: //
    object: //
    arrCity: [
        //
    ],
    arrEntry: [
        //
    ],
    arrObject: [
        //
    ]
}

我是 Node.js 开发人员，我很少使用 mysql（我正在处理实时数据）。

标签： mysql

mysql - Mysql JOIN 几行多行的表

问题描述

解决方案

推荐阅读