mysql where匹配所有,MySQL + where子句匹配多行

I have a table like this:

id image_id style_id

------------------------

1 45 24

1 45 25

1 47 25

1 45 27

1 45 28

I want to pull image_id column where all three below conditions match:

style_id = 24

style_id = 25

style_id = 27

I have a query like this:

SELECT image_id FROM list WHERE (style_id = 24 AND style_id = 25 AND style_id = 27)

which doesn't return me image_id 45.

解决方案

Try this:

SELECT image_id

FROM list

WHERE style_id IN (24, 25, 27)

GROUP BY image_id

HAVING COUNT(DISTINCT style_id) = 3

The DISTINCT keyword is only necessary in case you can have duplicate values of style_id field per image_id.