mysql - SQL Query比较不同行中的数据

我昨天在一次求职面试中遇到了这个问题，虽然它看起来很直接，但我无法弄清楚，它让我整晚都在思考它。

系统记录了一个拼字游戏联赛的数据，有一个成员表、一个游戏表和一个加入的 member_games 表。

members: member_id, name : pk(member_id)
member_games: game_id, member_id, score : pk(game_id, member_id)
games: game_id, location, date : pk(game_id)

members
1, mick
2, keith
3, charlie

member_games
1, 1, 50
1, 2, 60
2, 1, 45
2, 3, 105
3, 1, 30
3, 3, 120

game
1, london, 2012-12-01
2, richmond, 2012-12-02
3, leeds, 2012-12-03

您如何制定 SQL 查询以找出 member_id = 1 的获胜次数？

最佳答案

以下查询将为您提供 member_id 1 的获胜次数。

select count(*) as number_of_wins
    from member_games as mg1
    where member_id = 1
    and not exists (select 1
        from member_games as mg2
        where mg2.game_id = mg1.game_id
        and mg2.member_id <> mg1.member_id
        and mg2.score >= mg1.score);

以下查询将为您提供具有各自获胜次数的成员列表。

select m.*,
    (select count(*) from member_games as mg1
        where member_id = m.member_id
        and not exists (select 1
            from member_games as mg2
            where mg2.game_id = mg1.game_id
            and mg2.member_id <> mg1.member_id
            and mg2.score >= mg1.score)
    ) as number_of_wins
    from members as m;

这些查询不将第一名的平局视为胜利。

我创建了一个 MySQL SQL Fiddle demo 和一个 SQL Server SQL Fiddle demo 。

关于mysql - SQL Query比较不同行中的数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14765854/