找到两个游戏都玩过的玩家

Question

我想获得那些同时下国际象棋和跳棋的玩家的playerid。我有一个这样的表:

playerid   game         website
a1         chess        www.abc.com
a2         checkers     www.cba.com
a1         checkers     www.abc.com
b2         chess        www.abc.com
b1         chess        www.abc.com
a3         checkers     www.aaa.com
b2         checkers     www.abc.com

期望输出(a3和b1应排除在外)

a1
b2

select game, playerid, website
from player_db
where (game= 'chess' or game= 'checkers') and website='abc.com'
group by playerid ;

这是我的SQL，但它似乎没有得到两个游戏-它选择国际象棋或跳棋。

Answer 1

使用OR时的问题是，也会发现每个条目只满足两个条件中的一个，但您只希望获得满足两个条件的条目。使用AND时的问题(这个答案似乎又被删除了)将是你不会得到任何行，因为游戏不能等于"国际象棋"。和";checker"同一时间。所以，这意味着你将需要两个查询，并检查哪些玩家出现在两个查询中，一个检查game = "chess"还有一个是game="checker"有不同的选择。一种可能是使用IN子句:

SELECT DISTINCT playerid
FROM daily_player
WHERE playerid IN (SELECT playerid FROM daily_player WHERE game = 'chess')
AND playerid IN (SELECT playerid FROM daily_player WHERE game = 'checkers')
AND website='abc.com';

另一种方法是使用EXISTS:

SELECT DISTINCT playerid
FROM daily_player d1
WHERE EXISTS (SELECT 1 FROM daily_player WHERE game = 'chess' AND playerid = d1.playerid)
AND EXISTS (SELECT 1 FROM daily_player WHERE game = 'checkers' AND playerid = d1.playerid) 
AND website='abc.com'

请注意您期望的结果"a1 b2"只有在使用DISTINCT而不选择不同的列(如game等)时才能实现。如果你仍然想选择它们，你当然可以这样做:

SELECT DISTINCT playerid,game,website
FROM daily_player
WHERE playerid IN (SELECT playerid FROM daily_player WHERE game = 'chess')
AND playerid IN (SELECT playerid FROM daily_player WHERE game = 'checkers')
AND website='abc.com';

但是您将不会得到您所显示的结果，但是在本例中是4行而不是2行。如果你删除上面的游戏选择，你将再次得到两行，因为网站没有不同。

Answer 2

一个带有多个子查询的小长查询就可以了。

SELECT playerid FROM daily_player a
WHERE 
(SELECT COUNT(*) FROM daily_player WHERE game ='chess' AND playerid=a.`playerid`)=1 
AND
(SELECT COUNT(*) FROM daily_player WHERE game ='checkers' AND playerid=a.`playerid`)=1
AND website='www.abc.com'
GROUP BY playerid;

结果

playerid  
----------
a1        
b2 

解释:

1. 1子查询评估玩家是否下棋。
   
2. 二子查询:评估玩家是否玩过游戏检查器。
3. 使用AND检查两个子查询是否都是TRUE。
4. 添加另一个AND来评估网站

注意:如果每个玩家都可以多次玩一个游戏，你可以用>=1代替=1。

Answer 3

如果不想要很长的查询，可以使用join函数。比如:

create table chess as select * from daily_player where game = 'chess';
create table checkers as select * from daily_player where game = 'checkers';
select chess.playerid from chess inner join checkers
on chess.playerid = checkers.playerid;

Answer 4

我通常通过"HAVING ">在一列中发现重复项。.

如果你只有两个游戏，这是最短的方法。

SELECT playerid
FROM daily_player
GROUP BY playerid 
HAVING COUNT(playerid ) > 1;    

Answer 5

select DISTINCT t1.player_id from daily_player as t1
where exists (select 1 from daily_player as t2 where t1.player_id = t2.player_id and game = 'chess')
and exists (select 1 from daily_player as t3 where t1.player_id = t3.player_id and game = 'checkers')
and  website='abc.com'

Answer 6

如果您想获取同时下国际象棋和跳棋的playerid，那么查询将是:

SELECT playerid
FROM daily_player
WHERE game in ('checkers', 'chess') && website = 'www.abc.com' GROUP by playerid HAVING COUNT(*)=2;