MySQL 多个 Where 子句

MySQL Multiple Where Clause

提问by Bugra YUKSEL

I have a table like this:

我有一张这样的表：

id  image_id  style_id  style_value
-----------------------------------
1   45        24        red
1   45        25        big
1   47        26        small
1   45        27        round
1   49        28        rect

I want to take image_id column if:

如果出现以下情况，我想采用 image_id 列：

• style_id = 24and style_value = red
• style_id = 25and style_value = big
• style_id = 26and style_value = round

• style_id = 24和 style_value = red
• style_id = 25和 style_value = big
• style_id = 26和 style_value = round

I have make a query like this:

我有这样的查询：

$query = mysql_query("SELECT image_id FROM list WHERE (style_id = 24 AND style_value = 'red') AND (style_id = 25 AND style_value = 'big') AND (style_id = 27 AND style_value = 'round')

But I couldn't get any result. When I make this sample with OR, it works well. But I have to do this with AND. Because I need image id s which are both "red, big and rect".

但我无法得到任何结果。当我用 OR 制作这个样本时，效果很好。但我必须用AND来做到这一点。因为我需要图像 id 都是“红色，大和矩形”。

I have made lots of search with Google but couldn't fine any solution.

我用 Google 进行了大量搜索，但找不到任何解决方案。

回答by fthiella

I think that you are after this:

我认为你在追求这个：

SELECT image_id
FROM list
WHERE (style_id, style_value) IN ((24,'red'),(25,'big'),(27,'round'))
GROUP BY image_id
HAVING count(distinct style_id, style_value)=3

You can't use AND, because values can't be 24 redand 25 bigand 27 roundat the same time in the same row, but you need to check the presence of style_id, style_valuein multiple rows, under the same image_id.

您不能使用 AND，因为值不能24 red和25 big和27 round在同一行中同时存在，但您需要检查style_id, style_value同一image_id.

In this query I'm using IN (that, in this particular example, is equivalent to an OR), and I am counting the distinct rows that match. If 3 distinct rows match, it means that all 3 attributes are present for that image_id, and my query will return it.

在这个查询中，我使用了 IN（在这个特定的例子中，它相当于一个 OR），并且我正在计算匹配的不同行。如果 3 个不同的行匹配，则意味着该 3 个属性都存在image_id，我的查询将返回它。

回答by bobwienholt

SELECT a.image_id 
FROM list a
INNER JOIN list b
   ON a.image_id = b.image_id
   AND b.style_id = 25
   AND b.style_value = 'big'
INNER JOIN list c
   ON a.image_id = c.image_id
   AND c.style_id = 27
   AND c.style_value = 'round'
WHERE a.style_id = 24 
   AND a.style_value = 'red'

回答by akashivskyy

You will never get a result, it's a simple logic error.

你永远不会得到结果，这是一个简单的逻辑错误。

You're asking your database to return a row which has style_id = 24 AND style_id = 25 AND style_id = 26. Since 24 is niether 25 nor 26, you will get no result.

您要求您的数据库返回具有style_id = 24 AND style_id = 25 AND style_id = 26. 由于 24 既不是 25 也不是 26，因此您不会得到任何结果。

You have to use OR, then it makes some sense.

您必须使用OR，然后才有意义。

回答by Lock

This might be what you are after, although depending on how many style_id's there are, it would be tricky to implement (not sure if those style_id's are static or not). If this is the case, then it is not really possible what you are wanting as the WHERE clause works on a row to row basis.

这可能就是您所追求的，尽管取决于有多少 style_id，实现起来会很棘手（不确定那些 style_id 是否是静态的）。如果是这种情况，那么 WHERE 子句在行到行的基础上工作，因此您不可能真正想要什么。

WITH cte as (
  SELECT
    image_id,
    max(decode(style_id,24,style_value)) AS style_colour,
    max(decode(style_id,25,style_value)) AS style_size,
    max(decode(style_id,27,style_value)) AS style_shape
  FROM
    list
  GROUP BY
    image_id
)
SELECT
  image_id
FROM
  cte
WHERE
  style_colour = 'red'
  and style_size = 'big'
  and style_shape = 'round'

http://sqlfiddle.com/#!4/fa5cf/18

http://sqlfiddle.com/#!4/fa5cf/18

回答by Keith John Hutchison

select unique red24.image_id from 
( 
    select image_id from `list` where style_id = 24 and style_value = 'red' 
) red24
inner join 
( 
    select image_id from `list` where style_id = 25 and style_value = 'big' 
) big25
on red24.image_id = big25.image_id
inner join 
( 
    select image_id from `list` where style_id = 27 and style_value = 'round' 
) round27
on red24.image_id = round27.image_id

回答by Ajay Gupta

May be using this query you don't get any result or empty result. You need to use ORinstead of ANDin your query like below.

可能正在使用此查询，您没有得到任何结果或空结果。您需要在查询中使用OR而不是AND如下所示。

$query = mysql_query("SELECT image_id FROM list WHERE (style_id = 24 AND style_value = 'red') OR (style_id = 25 AND style_value = 'big') OR (style_id = 27 AND style_value = 'round');

Try out this query.

试试这个查询。