MySQL Multiple Where子句 [英] MySQL Multiple Where Clause

问题描述

我有一个这样的表:

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

在以下情况下，我想获取image_id列:

I want to take image_id column if:

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

• style_id = 24 and style_value = red
• style_id = 25 and style_value = big
• style_id = 26 and 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')

但是我没有任何结果.当我用OR制作此样本时，它可以很好地工作.但是我必须使用AND来做到这一点.因为我需要的图像ID都是红色，大而矩形".

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".

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

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

推荐答案

我认为您在此之后:

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

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

You can't use AND, because values can't be 24 red and 25 big and 27 round at the same time in the same row, but you need to check the presence of style_id, style_value in multiple rows, under the same image_id.

在此查询中，我使用IN(在此特定示例中，它等效于OR)，并且我在计算匹配的不同行.如果3个不同的行匹配，则表示该image_id的所有3个属性都存在，我的查询将返回它.

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.

这篇关于MySQL Multiple Where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！