SQL查询工作，但数据正在损坏？

Question

我有一个查询，当只为一个游戏WHERE gameid = 36搜索数据时，这个查询很好，但是一旦我尝试加入多个游戏gameid = 36 AND gameid = 37，这个查询就会运行良好，但是对于所有数据返回所有的0。

SELECT 
 CONCAT_WS(  '. ', SUBSTR( p.playerfname, 1, 1 ) , p.playerlname ) name, 
 COALESCE( goalEvents.goals, 0 ) goals, 
 COALESCE( a1.assists, 0 ) + COALESCE( a2.assists, 0 ) assists, 
 COALESCE( goalEvents.goals, 0 ) + COALESCE( a1.assists, 0 ) + COALESCE( a2.assists, 0 ) points
FROM players p
 LEFT JOIN (SELECT scorer, COUNT( scorer ) goals FROM goalEvents WHERE gameid = 36 AND gameid = 37GROUP BY scorer) goalEvents ON p.playerid = goalEvents.scorer
 LEFT JOIN (SELECT assist1, COUNT( assist1 ) assists FROM goalEvents WHERE gameid = 36 AND gameid = 37 GROUP BY assist1) a1 ON p.playerid = a1.assist1
 LEFT JOIN (SELECT assist2, COUNT( assist2 ) assists FROM goalEvents WHERE gameid = 36 AND gameid = 37 GROUP BY assist2) a2 ON p.playerid = a2.assist2
WHERE p.playerteam = 26 ORDER BY points DESC, goals DESC

在调用多个游戏线时，必须发生错误。

FROM goalEvents WHERE gameid = 36 AND gameid = 37 

我是不是被多个游戏搜索错了？我基本上是在我桌子上的最后4个游戏中搜索

Answer 1

尝试：

FROM goalEvents WHERE gameid = 36 OR gameid = 37

您将需要使用OR而不是AND，gameid永远不会是36 AND 37。您希望找到两种不同的记录，一种是gameid=36记录，另一种是gameid=37记录。如果使用AND，它们都不能满足条件。

如果你有超过2，你也可以尝试gameid IN (36, 37, ...) -它是OR，OR，OR等的缩写

Answer 2

你可以使用IN子句--我想这能起作用

就像试试这个

FROM goalEvents WHERE gameid in(36,37)

祝你好运。

Answer 3

试试这个：

FROM goalEvents WHERE gameid in (36, 37);

我想gameid是唯一的，所以您不能使用and，因为不可能将gameid设为36和37。所以尝试使用in