代码之家 › 专栏 › 技术社区 › 0leg

MySQL左连接,其中第二个对象是AND

having-clause where-clause count sql mysql

0leg · 技术社区 · 4 年前

数据库

产品

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(128) | NO   | UNI | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

标签

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| name          | varchar(128) | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

产品标签

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11) | NO   | MUL | NULL    |                |
| tag_id     | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+

目标

返回产品由2+个标签标记的,例如“礼物”和“生日”。

它可能看起来像:

SELECT p.name FROM products p
LEFT JOIN products_tags p_t ON p_t.product_id = p.id
LEFT JOIN tags t ON t.id = p_t.tag_id
WHERE <what-is-the-condition?>

缺少按“礼物”和“生日”标签选择的正确条件。

类似于:

WHERE t.name = 'present' AND t.name = 'birthday';

2 回复 | 直到 4 年前

GMB 4 年前

你可以使用 group by 和 having :

SELECT p.name 
FROM products p
INNER JOIN products_tags p_t ON p_t.product_id = p.id
INNER JOIN tags t            ON t.id = p_t.tag_id
WHERE t.tag IN ('present', 'birthday')    -- either one or the other
GROUP BY p.id, p.name
HAVING COUNT(*) = 2                       -- both are present in the group

这假设每个产品没有重复的标签。否则,您需要更改 HAVING 条款至:

HAVING COUNT(DISTINCT t.tag) = 2

forpas 4 年前

桌子 tags 不需要。
加入我们 products 到 products_tags , group by 产品并在中设置条件 HAVING 条款:

SELECT p.id, p.name 
FROM products p INNER JOIN products_tags p_t 
ON p_t.product_id = p.id
GROUP BY p.id, p.name
HAVING COUNT(*) >= 2

Rajesh 4 年前

如果您的查询特定于任何标记,则WHERE是必须的。根据你期望的结果,我认为你的问题不正确。

SELECT products.name
    FROM  products
    Join products_tags ON products_tags.product_id = products.id
    
    JOIN tags ON tags.id = products_tags.tag_id
    WHERE tags.name = 'present' OR tags.name='birthday' GROUP BY products.name