MySQL选择多对多的位置 [英] MySQL Select Where In Many to Many

问题描述

我在使用SQL查询时遇到麻烦.我的架构描述了 articles 表中的文章与 categories 表中的类别之间的多对多关系-中间表 article_category id ， article_id 和 category_id 字段.

I'm having trouble with a SQL query. My schema describes a many to many relationship between articles in the articles table and categories in the categories table - with the intermediate table article_category which has an id, article_id and category_id field.

我想选择所有仅具有 id 1 和 2 类别的文章.不幸的是，此查询还将选择除其他类别外的所有具有这些类别的文章.

I want to select all articles which only have the categories with id 1 and 2. Unfortunately this query will also select any articles which have those categories in addition to any others.

例如，这是SQL的示例输出(出于描述目的显示类别).您可以看到，虽然查询选择了ID为 10 的文章，但尽管有一个额外的类别，它也选择了ID为 11 的文章.

For example, this is a sample output from the SQL (with categories shown for descriptive purposes). You can see that while the query selected the article with id of 10, it also selected the article with an id of 11 despite having one extra category.

+-------+------------+
| id    | categories |
+-------+------------+
| 10    | 1,2        |
| 11    | 1,2,3      |
+-------+------------+

这是我想要选择仅类别为 1 和 2 的文章的输出.

This is the output that I want to achieve from selecting articles with only categories 1and 2.

+-------+------------+
| id    | categories |
+-------+------------+
| 10    | 1,2        |
+-------+------------+

同样，这是我要选择仅类别为 1 ， 2 和 3 的文章所要实现的输出.

Likewise, this is the output what I want to achieve from selecting articles with only categories 1, 2 and 3.

+-------+------------+
| id    | categories |
+-------+------------+
| 11    | 1,2,3      |
+-------+------------+

这是我编写的SQL.要实现上述目标，我缺少什么?

This is the SQL I have written. What am I missing to achieve the above?

SELECT articles.id
FROM articles
WHERE EXISTS (
    SELECT 1
    FROM article_category
    WHERE articles.id = article_id AND category_id IN (1,2)
    GROUP BY article_id
)

非常感谢！

推荐答案

假设您想要的不仅仅是文章的ID:

Assuming you want more than just the article's id:

SELECT a.id
      ,a.other_stuff
  FROM articles a
  JOIN article_category ac
    ON ac.article_id = a.id
GROUP BY a.id
HAVING GROUP_CONCAT(DISTINCT ac.category_id ORDER BY ac.category_id SEPARATOR ',') = '1,2'

如果你只想要文章的 id，那么试试这个:

If all you want is the article's id then try this:

SELECT article_id
  FROM article_category 
GROUP BY article_id
HAVING GROUP_CONCAT(DISTINCT category_id ORDER BY category_id SEPARATOR ',') = '1,2'

在 http://sqlfiddle.com/#!2/9d213/4上查看实际运行情况

还应该补充一点，这种方法的优点是它可以支持检查任意数量的类别，而不必更改查询.只需将"1,2"设置为字符串变量，然后更改传递给查询的内容即可.因此，您可以通过传递字符串"1,2,7"来轻松搜索具有类别1、2和7的文章.不需要其他联接.

Should also add that the advantage of this approach is that it can support the checking of any number of categories without having to change the query. Just make '1,2' a string variable and change what gets passed into the query. So, you could just as easily search for articles with categories 1, 2, and 7 by passing a string of '1,2,7'. No additional joins are needed.

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