mysql - 如果我在一个表中有一组相关的行，我如何用组中的索引来装饰它们？

Question

我有两个表score 和game。每个游戏都有 n 分数。我想为游戏的每个分数获取 n。所以我会得到:

round, score, game
==================
1      1      1
2      2      1
3      3      1
1      9      2
2      3      2

示例 fiddle :

http://sqlfiddle.com/#!2/56434/1

Answer 1

SELECT  RoundNo, Score, ID
FROM
        (
            select  a.ID,
                    b.Score,
                    @sum := if(@nme = a.ID, @sum ,0) + 1 RoundNo,
                    @nme := a.ID
            from    game a
                    INNER JOIN scores b
                      ON a.ID = b.game_ID
                    CROSS JOIN
                    (select @nme := '', @sum := 0) vars
            order   by  a.ID
        ) s
ORDER   BY ID

• SQLFiddle Demo

输出

╔═════════╦═══════╦════╗
║ ROUNDNO ║ SCORE ║ ID ║
╠═════════╬═══════╬════╣
║       1 ║    14 ║  1 ║
║       2 ║    10 ║  1 ║
║       1 ║    19 ║  2 ║
║       2 ║    20 ║  2 ║
║       3 ║    25 ║  2 ║
╚═════════╩═══════╩════╝