mysql - 如何将这三个 MYSQL 查询合而为一？

Question

我有这个查询:

SELECT da_clubs_categories.name AS cat_name,
    da_clubs_categories.id AS cat_id,
    da_deals.id,
    da_brands.id AS brand_id,
    da_brands.name AS brand_name
    FROM da_clubs_categories, da_deals, da_brands
    WHERE da_clubs_categories.fk_club_id = da_brands.fk_club_id
    AND da_brands.fk_club_id = 6
    AND da_deals.fk_brand_id = da_brands.id
    AND FIND_IN_SET(da_clubs_categories.id, da_deals.categories) > 0
    AND da_brands.active = 1
    AND da_deals.active = 1
    AND da_deals.date_start <= CURRENT_DATE()
    AND CURRENT_DATE() <= da_deals.date_end
    ORDER BY da_clubs_categories.name ASC, da_deals.date_start DESC

结果:

+--------------+--------+----+----------+------------+
| cat_name     | cat_id | id | brand_id | brand_name |
+--------------+--------+----+----------+------------+
| Deporte      |     19 | 22 |       57 | Marca1     |
| Deporte      |     19 | 23 |       57 | Marca1     |
| Deporte      |     19 | 24 |       57 | Marca1     |
| Deporte      |     19 | 25 |       57 | Marca1     |
| Deporte      |     19 | 26 |       57 | Marca1     |
| Deporte      |     19 | 27 |       57 | Marca1     |
| Deporte      |     19 | 28 |       57 | Marca1     |
| Deporte      |     19 | 29 |       57 | Marca1     |
| Gastronomía  |     20 | 22 |       57 | Marca1     |
| Salud        |     21 | 22 |       57 | Marca1     |
+--------------+--------+----+----------+------------+

稍后，另一个查询:

SELECT value AS 'like'
FROM da_logs
WHERE fk_deal_id = 22
AND type = 'deal_like'

结果:

+------+
| like |
+------+
| ::1  |
+------+   

稍后，另一个查询:

SELECT value AS 'rating'
FROM da_logs
WHERE fk_deal_id = 22
AND type = 'deal_rating'

结果:

+------------------------------------------------------------+
| rating                                                     |
+------------------------------------------------------------+
| a:2:{s:7:"user_ip";s:3:"::1";s:12:"rating_value";s:1:"3";} |
+------------------------------------------------------------+

我必须使用 PHP 中的循环来做到这一点，但我只想进行一个查询，特别是带有两个附加列的 tableA(我做了 2 个附加查询)，如您所见。

如果未找到特定 DEAL 的点赞或评分，则将其设置为“”。

Answer 1

使用LEFT JOIN与da_logs表连接两次，针对您想要匹配的每个类型连接一次。

SELECT da_clubs_categories.name AS cat_name,
    da_clubs_categories.id AS cat_id,
    da_deals.id,
    da_brands.id AS brand_id,
    da_brands.name AS brand_name,
    IFNULL(l1.value, '') AS like,
    IFNULL(l2.value, '') AS rating
    FROM da_clubs_categories
    INNER JOIN da_brands ON da_clubs_categories.fk_club_id = da_brands.fk_club_id
    INNER JOIN da_deals ON da_deals.fk_brand_id = da_brands.id
        AND FIND_IN_SET(da_clubs_categories.id, da_deals.categories) > 0
    LEFT JOIN da_logs AS l1 ON l1.fk_deal_id = da_deals.id AND l1.type = 'deal_like'
    LEFT JOIN da_logs AS l2 ON l2.fk_deal_id = da_deals.id AND l2.type = 'deal_rating'
    WHERE da_brands.fk_club_id = 6
    AND da_brands.active = 1
    AND da_deals.active = 1
    AND da_deals.date_start <= CURRENT_DATE()
    AND CURRENT_DATE() <= da_deals.date_end
    ORDER BY da_clubs_categories.name ASC, da_deals.date_start DESC