sql—如何在mysql中选择多个列值等于一组特定值的记录?

gfttwv5a  于 2021-06-15  发布在  Mysql
关注(0)|答案(4)|浏览(537)

我有两张table

games

idGame  guidUser  
-----------------
12       2        
13       3

wordstyped

idGame  guidUser  word
-------------------------
12       2        honey
12       2        apples
13       3        bread
13       3        water
14       4        foo
15       5        bar
16       2        bartender
12       4        bar

我想在wordstyped中选择游戏中包含情侣的记录(idgame,guiduser)。
如果要搜索的值是我唯一能做的

SELECT * FROM wordstyped WHERE guidUser IN (SELECT guidUser from games)

但是如果要比较的值是几个值呢?
对于工作查询,在本例中,只会选择wordstyped的前4条记录。

ct2axkht

ct2axkht1#

在mysql中,您可以执行以下操作:

SELECT *
FROM wordstyped
WHERE (idGame, guidUser) IN (
    SELECT idGame, guidUser
    FROM games
);

它会匹配的 idGame, guidUser 对子查询进行配对。但是,我建议遵循以下标准:

SELECT *
FROM wordstyped
WHERE EXISTS (
    SELECT 1
    FROM games
    WHERE idGame = wordstyped.idGame AND guidUser = wordstyped.guidUser
);
nhn9ugyo

nhn9ugyo2#

可以使用内部联接并在联接条件中匹配多个字段

SELECT wordstyped.* FROM wordstyped 
INNER JOIN games ON 
         wordstyped.guidUser = games.guidUser 
         AND games.idGame = wordstyped.idGame
2cmtqfgy

2cmtqfgy3#

这应该能解决你的问题

SELECT * FROM wordstyped w
JOIN games g 
ON w.idGame=g.idGame
AND w.guidUser=g.guidUser
k97glaaz

k97glaaz4#

以下是连接解决方案:

SELECT w.idGame, w.guidUser, w.word
FROM wordstyped w
INNER JOIN games g
    ON w.idGame = g.idGame AND w.guidUser = g.guidUser;

演示

相关问题