MySQL严格选择涉及多对多表的行 [英] MySQL strict select of rows involving many to many tables

问题描述

我有多对多格式的三个表.也就是说，表A，B和AB的设置符合您的期望.

I have three tables in the many-to-many format. I.e, table A, B, and AB set up as you'd expect.

给定一些A ID，我只需要选择AB中与所有ID匹配的行.

Given some set of A ids, I need to select only the rows in AB that match all of the ids.

以下内容无法正常工作:

Something like the following won't work:

从AB处选择* A_id = 1 AND A_id = 2 AND A_id = 3 AND ..." 因为没有一行的A_id会超过一个

"SELECT * FROM AB WHERE A_id = 1 AND A_id = 2 AND A_id = 3 AND ... " As no single row will have more than one A_id

使用sql语句中的OR并不更好，因为它会产生具有至少一个A id的所有结果(而我只希望那些具有所有id的行).

Using, an OR in the sql statment is no better as it yields results all results that have at least one of the A ids (whereas I only want those rows that have all of the ids).

对不起，我应该解释一下.我不知道实际的多对多关系是否与实际的问题有关.这些表格概述如下:

Sorry, I should explain. I don't know if the actual many-to-many relationship is relevant to the actual problem. The tables are outlined as follows:

Table People
int id
char name

Table Options
int id
char option

Table peoples_options
int id
int people_id
int option_id

因此，我有一个人员列表，一个选项列表以及一个选项和人员表.

And so I have a list of people, and a list of options, and a table of options and people.

因此，给定选项ID的列表，例如(1，34，44，...)，我只需要选择那些拥有所有选项的人.

So, given a list of option ids such as (1, 34, 44, ...), I need to select only those people that have all the options.

推荐答案

有点棘手的解决方案是将IN与group by一起使用并具有过滤器.像这样:

A bit of a hacky solution is to use IN with a group by and having filter. Like so:

SELECT B_id FROM AB
WHERE A_id IN (1,2,3)
GROUP BY B_id
HAVING COUNT(DISTINCT A_id) = 3;

这样，您只能获得正好具有3个A_id值的B_id值，并且它们必须来自列表.我在COUNT中使用DISTINCT以防万一(A_id，B_id)不唯一.如果需要其他列，则可以在另一个select语句的FROM子句中作为子选择加入该查询.

That way, you only get the B_id values that have exactly 3 A_id values, and they have to be from your list. I used DISTINCT in the COUNT just in case (A_id, B_id) isn't unique. If you need other columns, you could then join to this query as a sub-select in the FROM clause of another select statement.

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