我有这个查询:
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 的喜欢或评级,则将其设置为 ''。
使用 LEFT JOIN
与da_logs
表联接两次,要匹配的每个type
一次。
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