MySQL select join where AND where_数据库问题-得得之家

本文介绍了MySQL select join where AND where的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的数据库中有两个表:

I have two tables in my database:

产品

id(整数，主键)
名称(varchar)

产品标签

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

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

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.

推荐答案

这种类型的问题被称为关系划分

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*/

这篇关于MySQL select join where AND where的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！

MySQL select join where AND where

问题描述

推荐答案

相关文档推荐