sql - 查找具有所有 "win"、 "draw"和 "loss"结果的玩家

Question

player1_id | score1 | score2 | player2_id
-----------+--------+--------+-----------
         1 |      1 |      1 |          2
         3 |      1 |      1 |          1
        11 |      1 |      0 |         20
         5 |      1 |      1 |         55
       200 |      1 |      2 |         11
        17 |      1 |      1 |          7
        11 |      1 |      3 |          4
        11 |      1 |      1 |        100
        20 |      1 |      1 |          2
        20 |      2 |      1 |         33

玩家根据score1和score2有“赢”、“平”或“输”的结果。我需要找到所有“赢”、“平”和“输”结果的玩家。在这种情况下，玩家 11 和 20。

我被困在这里，非常感谢任何帮助。

Answer 1

如果我没理解错的话，你需要这个:

select p from (
    select player1_id as p, case when score1>score2 then 'W' when score1=score2 then 'D' when score1<score2 then 'L' end as res from your_table
    union all
    select player2_id as p, case when score1>score2 then 'L' when score1=score2 then 'D' when score1<score2 then 'W' end as res from your_table
) t
group by p
having count( distinct res ) = 3