mysql 两列计数_MYSQL - 如何在两列上返回唯一计数(MYSQL - how to return unique count on two columns)...

I'm trying to solve this query where I have two players with game scores. The query should return a chart of the players with the numbers of games and if the score was 3:0 or 0:3 then the player that score 3 gets 3 point otherwise any winning is for 2 points

My table:

player1 player2 player1score player2score

------- ------- ------------- ------------

john lee 3 0

maria andy 1 3

andy john 1 3

the desired table should be like this:

players gamesplayed points

------- ----------- ------

john 2 5

andy 2 2

maria 1 0

lee 1 0

I have this query:

SELECT DISTINCT players, count(*) gamesplayed

FROM (

SELECT player1 AS players

FROM table

UNION

SELECT player2 AS players

FROM table

) players

But the query above returns the count of all players together and not individually in gamesplayed.

Can anyone see the problem? I'm also not sure hot implement the points column.

Many thanks