count和join必须在表中包含另一个中不存在的记录

see http://sqlfiddle.com/#!2/26ee6

SELECT g.gameID, (count(h.idx)%2) as lastmove
FROM games as g
  inner join history as h on g.gameID=h.gameID 
WHERE (g.whitePlayer=2 or g.blackPlayer=2)
GROUP by g.gameID, g.whitePlayer, g.blackplayer
HAVING (g.blackPlayer=2 AND lastmove=1)
OR (g.whitePlayer=2 AND lastmove=0)

Preferred RESULTS:

gameID--lastmove - really move  - is it player=2 's move ?
256 ---- null --=> white -> player2= white = yes its move => out
414 ----  1 ----=> black -> player2= white   = not
497 ----  0 --- => white -> player2= white = yes its move => out
498 ----  1 --- => black -> player2= black = yes its move => out

I am trying to get ALL GAMES where

我想把所有游戏都放到哪里

(g.blackPlayer=2 AND lastmove=1) OR (g.whitePlayer=2 AND lastmove=0)

Problem is for game 256, there are no records in history.

问题是游戏256,历史上没有记录。

(count(h.idx)%2) as lastmove  returns 0 and that is good

but the gameID is not in the Join and therefore gameID=256 is missed completely!

但是gameID不在Join中,因此完全错过了gameID = 256!

If i Join the otherway around i can not implement the COUNT without errors, and anyway that missed the game 256 as well:

如果我加入其他方式我无法实现COUNT而没有错误,无论如何也错过了游戏256:

SELECT g.gameID
FROM games as g
  inner join history as h on g.gameID=h.gameID 
WHERE (g.whitePlayer=2 or g.blackPlayer=2)
GROUP by g.gameID, g.whitePlayer, g.blackplayer
HAVING (g.blackPlayer=2 AND ((count(h.idx)%2)=1) -error
OR (g.whitePlayer=2 AND ((count(h.idx)%2)=0)  -error

http://sqlfiddle.com/#!2/26ee6

Who can help solve this problem? Thank you.

谁能帮忙解决这个问题?谢谢。

1 个解决方案

#1

I'm not 100% sure i understood your problem, but if you want to include game 256, you should do a LEFT JOIN instead of the inner join:

我不是100%确定我理解你的问题,但是如果你想要包含游戏256,你应该做一个LEFT JOIN而不是内连接:

SELECT g.gameID, (count(h.idx)%2) as lastmove
FROM games AS g
  LEFT JOIN history AS h ON g.gameID=h.gameID 
WHERE (g.whitePlayer=2 OR g.blackPlayer=2)
GROUP BY g.gameID, g.whitePlayer, g.blackplayer
HAVING (g.blackPlayer=2 AND lastmove=1)
OR (g.whitePlayer=2 AND lastmove=0);

In your second query you are missing a couple of ).

在您的第二个查询中,您错过了几个)。

If you do it with left join it would be:

如果你用左连接这样做,那将是:

SELECT g.gameID
FROM games as g
  LEFT JOIN history AS h ON g.gameID=h.gameID 
WHERE (g.whitePlayer=2 OR g.blackPlayer=2)
GROUP BY g.gameID, g.whitePlayer, g.blackplayer
HAVING (g.blackPlayer=2 AND ((count(h.idx)%2)=1))
OR (g.whitePlayer=2 AND ((count(h.idx)%2)=0))  ;

See sqlfiddle

#1