sql - SQL SELECT 查询的问题

Question

我有一个包含此信息的表 GAMES:

- Id_Game (int)
- Id_Referee1 (int)
- Id_Referee2 (int)

这些是一些行:

ID_GAME    ID_REFEREE1    ID_REFEREE2
1          1000           728
2          1004           813
3          728            1004
4          1004           1000

我想要一个包含裁判主持的比赛数量的列表，无论是作为裁判 1 还是裁判 2。在这种情况下，我想按游戏数 DESC 接收此列表顺序。

REFEREE    NUMBER OF GAMES
1004       3
728        2
1000       2
813        1

哪个是 SQL 查询？我不知道如何加入字段 Id_Referee1 和 Id_Referee2 ...

谢谢

Answer 1

我认为:

select referee, count(*) from (
    select id_game, id_referee1 as referee
    from games
    union
    select id_game, id_referee2 as referee
    from games
)
group by referee