选择字符和组与连接?

I have the following table setup in mysql:

我在mysql中有如下表设置:

CREATE TABLE `games_characters` (
  `game_id` int(11) DEFAULT NULL,
  `player_id` int(11) DEFAULT NULL,
  `character_id` int(11) DEFAULT NULL,
  KEY `game_id_key` (`game_id`),
  KEY `character_id_key` (`character_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My objective is to get a game_id where a list of character_ids are all present in this game_id.

我的目标是获得一个game_id，其中一个字符id列表都在这个game_id中。

An example set of data:

一组数据示例:

1, 1
1, 2
1, 3
2, 1
2, 2
3, 1
3, 4

Let's say i want to get the game_id where the character_id has 1, 2, and 3. How would I go about making an efficient query? Best idea I have had so far was joining the table to itself multiple times, but i assume there has to be a better way to do this.

假设我想要获得game_id，其中字符id有1 2 3。如何进行有效的查询?到目前为止，我的最佳想法是多次加入这个表，但我认为必须有更好的方法来实现这一点。

Thanks

谢谢

EDIT: for anyone curious this was the final solution I used as it proved the best query time:

编辑:对于任何好奇的人来说，这是我使用的最终解决方案，因为它证明了最好的查询时间:

SELECT game_ID
FROM (
    SELECT DISTINCT character_ID, game_ID
    FROM games_Characters
) AS T
WHERE character_ID
IN ( 1, 2, 3 ) 
GROUP BY game_ID
HAVING COUNT( * ) =3

2 个解决方案

#1

Select game_ID from games_Characters
where character_ID in (1,2,3)
group by game_ID
having count(*) = 3

the above makes two assumptions
1) you know the characters your looking for
2) game_ID and character_ID are unique

I don't assume you can get the #3 for the count I knnow you can since you know the list of people you're looking for.

我不认为你能得到我现在知道的数字的3，因为你知道你要找的人的名单。

#2

This ought to do it.

这是应该的。

select game_id
from games_characters
where character_id in (1,2,3)
group by game_id
having count(*) = 3

If that's not dynamic enough for you you'll need to add a few more steps.

如果这对您来说不够动态，您需要添加更多的步骤。

create temporary table character_ids(id int primary key);

insert into character_ids values (1),(2),(3);

select @count := count(*)
from character_ids;

select gc.game_id
from games_characters as gc
join character_ids as c
    on (gc.character_id = c.id)
group by gc.game_id
having count(*) = @count;

#1

Select game_ID from games_Characters
where character_ID in (1,2,3)
group by game_ID
having count(*) = 3

the above makes two assumptions
1) you know the characters your looking for
2) game_ID and character_ID are unique