Improving sql query for selecting amount of games played and amount of games won by player

I've got some problem with sql-query I can't solve.

开发者_开发技巧

I have 2 tables:

players: id, name
games: id, name, player1_id, player2_id, winner_id

Now I want to get total amount of games and wins for each player.

Currently I'm doing it with sub-selects:

SELECT player.name AS name, 
(SELECT COUNT(id) FROM replays AS replay WHERE replay.player1_id = player.id OR replay.player2_id = player.id) AS games,
(SELECT COUNT(id) FROM replays AS replay WHERE replay.winner_id = player.id) AS wins
FROM players AS player

But first of all it is terribly slow. And than I am not able to make complicated search queries like search for players with wins more than smth or games less that smth.

Do you have any ideas how can I improve this query?

Thank you in advance.

---

Try something like this:

select a.player_id, p.name, count(*) as GamesCount, 
    count(case when g.winner_id = a.player_id then 1 end) as WinsCount
from (
    select player1_id as player_id
    from games 
    union
    select player2_id as player_id
    from games 
) a
inner join games g on a.player_id = g.player1_id 
    or a.player_id = g.player2_id 
inner join players p on a.player_id = p.id
group by a.player_id, p.name