使用MySQL IN子句作为全包(AND而不是OR) [英] Using MySQL IN clause as all inclusive (AND instead of OR)

问题描述

我在网站上列出了商品列表.每个项目都有几个附加的类别，我们称这些类别为电影流派.在高级搜索中，我让人们选中他们喜欢的流派，然后它弹出与所选流派中的任何一种都匹配的电影列表.

I have a listing of items on a site. Each item has several categories attached to it, let's call these categories movie genres. In the advanced search, I let people check boxes for the genres they like, and it spits out the list of movies matching ANY of the selected genres.

我有这样的查询:

AND column IN ('5', '8', '9')

这里的问题是，如果您选择动画"和恐怖"，则会得到一堆迪斯尼卡通(动画")和SAW系列(恐怖").

The problem here is that if you select "animation" and "horror" you will get a bunch of Disney cartoons ("animation") and the SAW series ("horror").

我想将搜索范围包括在内，因此它只会返回与所有选定流派都匹配的结果，因此将同时返回标记为动画"和恐怖"的项目.

I wanted to adapt the search to be all inclusive, so it would only return results matching ALL of the selected genres, so items marked both 'animation' and 'horror' would be returned.

item_id和category_id对存储在单独的表中.因此，对于ID为55的电影，可能会有4种流派，所以会有4行具有item_id = 55，并且category_id等于4个类别ID.

The item_id and category_id pairs are stored in a separate table. So for a movie with an ID 55, there might be 4 genres, so there would be 4 rows with item_id = 55, and category_id equals the 4 category Ids.

推荐答案

编辑:修改了我的答案，使其与OP的已编辑问题更加匹配.

EDIT: Modified my answer to more closely match the OP's edited question.

select i.MovieName
    from item i
        inner join ItemCategory ic
            on i.item_id = ic.item_id
    where i.item_id = 55
        and ic.category_id in ('5','8','9')
    group by i.MovieName
    having count(distinct ic.category_id) = 3

这篇关于使用MySQL IN子句作为全包(AND而不是OR)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！