MySQL 选择连接 where AND where

发布于 2024-10-17 19:46:05 字数 539 浏览 6 评论 0原文

我的数据库中有两个表：

Products

id (int, Primary key)
name (varchar)

ProductTags

Product_id (int)
tag_id (int)

我想选择具有所有给定的标签。我尝试过：

SELECT
    *
FROM
    Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE
    ProductTags.tag_id IN (1, 2, 3)
GROUP BY
    Products.id

但它为我提供了具有任何给定标签的产品，而不是具有所有给定标签的产品。编写 WHERE tag_id = 1 AND tag_id = 2 是没有意义的，因为不会返回任何行。

原文

I have two tables in my database:

Products

id (int, primary key)
name (varchar)

ProductTags

product_id (int)
tag_id (int)

I would like to select products having all given tags. I tried:

SELECT
    *
FROM
    Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE
    ProductTags.tag_id IN (1, 2, 3)
GROUP BY
    Products.id

But it gives me products having any of given tags, instead of having all given tags. Writing WHERE tag_id = 1 AND tag_id = 2 is pointless, because no rows will be returned.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝咒 2024-10-24 19:46:05

此类问题称为关系划分

SELECT Products.* 
FROM Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id IN (1,2,3)
GROUP BY Products.id /*<--This is OK in MySQL other RDBMSs 
                          would want the whole SELECT list*/

HAVING COUNT(DISTINCT ProductTags.tag_id) = 3 /*Assuming that there is a unique
                                              constraint on product_id,tag_id you 
                                              don't need the DISTINCT*/

This type of problem is known as relational division

SELECT Products.* 
FROM Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id IN (1,2,3)
GROUP BY Products.id /*<--This is OK in MySQL other RDBMSs 
                          would want the whole SELECT list*/

HAVING COUNT(DISTINCT ProductTags.tag_id) = 3 /*Assuming that there is a unique
                                              constraint on product_id,tag_id you 
                                              don't need the DISTINCT*/

回复收藏 0 原文

多情癖 2024-10-24 19:46:05

您需要有一个分组依据/计数以确保所有内容都被考虑在内

select Products.*
  from Products 
         join ( SELECT Product_ID
                  FROM ProductTags
                  where ProductTags.tag_id IN (1,2,3)
                  GROUP BY Products.id
                  having count( distinct tag_id ) = 3 ) PreQuery
        on ON Products.id = PreQuery.product_id

you need to have a group by / count to ensure all are accounted for

select Products.*
  from Products 
         join ( SELECT Product_ID
                  FROM ProductTags
                  where ProductTags.tag_id IN (1,2,3)
                  GROUP BY Products.id
                  having count( distinct tag_id ) = 3 ) PreQuery
        on ON Products.id = PreQuery.product_id

回复收藏 0 原文

浮生面具三千个 2024-10-24 19:46:05

MySQL WHERE fieldname IN (1,2,3) 本质上是 WHERE fieldname = 1 OR fieldname = 2 OR fieldname = 3 的简写。因此，如果您无法使用 WHERE ... IN 获得所需的功能，请尝试切换到 OR。如果仍然没有给您想要的结果，那么也许 WHERE ... IN 不是您需要使用的函数。