mysql - 嵌套查询——有没有更聪明的方法

Question

我有一个关于嵌套/子查询的问题已经困扰我一段时间了。

我正在用 php 编写一个游戏。在我的系统的一个非常简单的版本中，我有以下 3 个表:

玩家(每个玩家只有一个条目):

| player_id | player_name |

统计数据(每个玩家在他参加的每一场比赛中都有一个条目):

| stat_id | player_id | game_id | outcome |

游戏(每个游戏有一个条目):

| game_id | game_name |

我想查询我的数据库并得到一个表，其中的行结构如下所示作为输出:

| player_name | count stats for player where outcome = 1 || count stats for player where outcome = 2 |

我有以下可行的方法，但我认为有一种更聪明、更好的方法:

select a.player_name,
    (select count(*) from stats b where b.player_id = a.player_id AND b.outcome = 1) as number_of_games1,
    (select count(*) from stats b where b.player_id = a.player_id AND b.outcome = 2) as number_of_games2 
from players a

我还希望能够在外部位置使用“number_of_games1”或“number_of_games2”，但上述方法不允许这样做:

select a.player_name,
    (select count(*) from stats b where b.player_id = a.player_id AND b.outcome = 1) as number_of_games1,
    (select count(*) from stats b where b.player_id = a.player_id AND b.outcome = 2) as number_of_games2 
from players a where number_of_games > 5

做我想做的事情最聪明的方法是什么？

谢谢。

Answer 1

您可以使用带条件的 SUM() 函数并使用连接，使用带条件的 SUM() 将导致 bool 值 1 或 0，因此您可以为 b.outcome = 1 和 b.outcome = 2

计算你想要的结果

SELECT 
a.player_id,
a.player_name,
SUM(b.outcome = 1) as number_of_games1,
SUM(b.outcome = 2) as number_of_games2 
FROM players a 
LEFT JOIN stats b ON(b.player_id = a.player_id)
GROUP BY a.player_id
HAVING number_of_games1 > 5 
/* you can add second condition for number_of_games2 
starting with any conditional operator  
*/