MYSQL - 如何返回两列的唯一计数

Question

我正在尝试解决这个查询，我有两个玩家有游戏分数。查询应返回包含比赛次数的球员图表，如果比分是 3:0 或 0:3，则得分为 3 的球员获得 3 分，否则任何获胜者获得 2 分

我的 table :

    player1        player2   player1score  player2score
    -------        -------   -------------  ------------
    john            lee           3             0 
    maria           andy          1             3
    andy            john          1             3

想要的表格应该是这样的:

    players        gamesplayed   points  
    -------        -----------   ------
    john            2               5 
    andy            2               2
    maria           1               0
    lee             1               0

我有这个问题:

SELECT DISTINCT players, count(*) gamesplayed
FROM (
    SELECT player1 AS players
    FROM table
    UNION
    SELECT player2 AS players
    FROM table
) players

但上面的查询返回所有玩家的总数，而不是在 gamesplayed 中单独的。

谁能看出问题所在？我也不确定是否热实现了 points 列。

非常感谢

Answer 1

现在觉得合适的MySQL-Statement，改成inline-view就好了，没有mysql环境，语法不对还请见谅:

SELECT player, 
    COUNT(player) AS gamesPlayed, SUM(score) score
FROM 
    (SELECT player1 AS player, 
        CASE 
            WHEN player1score - player2score > 2 THEN  + 3
            WHEN player1score - player2score > 0 THEN +2
            ELSE + 0
            END as score 
        FROM playerTable
    UNION ALL
    SELECT player2 AS player, 
        CASE 
        WHEN player2score - player1score > 2 THEN  + 3
        WHEN player2score - player1score > 0 THEN +2
        ELSE + 0
        END AS score
    FROM playerTable) 
    AS playerSel
Group by player;