sql - MYSQL 对同一类别进行多项选择？

Question

我在多对多关系中有 3 个表(场景、类别、场景类别)。

场景(id，标题，描述)类别(编号、标题)场景类别(场景 ID、类别 ID)

我在查询以选择必须匹配多个类别的场景时遇到问题。例如，我可能想选择匹配类别 3 AND 类别 5 和类别 8 的场景，但我不知道如何让它工作。

到目前为止，我有类似的东西

SELECT scenes.id, scenes.title, scenes.description
FROM scenes
LEFT JOIN scenes_categories ON scenes.id = scenes_categories.scene_id
LEFT JOIN categories ON scenes_categories.category_id = categories.id
WHERE scenes_categories.category_id = '3'
AND scenes_categories.category_id = '5'
AND scenes_categories.category_id = '8'
AND scenes.id = '1'

如何选择必须匹配所有指定类别 ID 的记录？

Answer 1

对于您需要的每个类别 ID，您需要要求该 sceneId 在多对多表中存在一行:所以试试这个:

SELECT s.id, s.title, s.description
FROM scenes s
WHERE s.id = '1'
   And Exists (Select * From scenes_categories 
               Where scene_id = s.Id
                  And category_id = '3')
   And Exists (Select * From scenes_categories 
               Where scene_id = s.Id
                  And category_id = '5')

   And Exists (Select * From scenes_categories 
               Where scene_id = s.Id
                  And category_id = '8')

另一个应该可行的选项是改为执行三个内部联接:

SELECT s.id, s.title, s.description
FROM scenes s
  Join scenes_categories c3 
      On c3.scene_id  = s.Id
           And c3.category_id ='3'
  Join scenes_categories c5 
      On c5.scene_id  = s.Id
           And c5.category_id ='5'
  Join scenes_categories c8 
      On c8.scene_id  = s.Id
           And c8.category_id ='8'     
WHERE s.id = '1'