MySQL计数值直到特定行

Question

Apologies if I'm not explaining my situation in perfect detail as I'm not a native speaker. I'm sure this is fairly simple for most people, but I struggle with it. I'm using php/mysql.

Suppose I have a database as follows:

id   game   scorer
1     1      molly
2     1      sarah
3     1      angela
4     1      molly
5     2      jane
6     2      molly
7     2      sarah

How would I write a query to present the scorers for each game with the number of goals they have scored during the season up until that particular goal?

Example: 
Game 1: Molly (1st), Sarah (1st), Angela (1st), Molly (2nd)
Game 2: Jane (1st), Molly (3rd), Sarah (2nd)

Answer 1

CREATE TABLE goals
(goal_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,game INT NOT NULL
,scorer VARCHAR(12) NOT NULL
);

INSERT INTO goals VALUES
(1,1,'molly'),
(2,1,'sarah'),
(3,1,'angela'),
(4,1,'molly'),
(5,2,'jane'),
(6,2,'molly'),
(7,2,'sarah');


SELECT game, GROUP_CONCAT(CONCAT(scorer,'(',rank,')')) scorers FROM 
(
SELECT x.game
     , x.scorer
     , COUNT(*) rank 
  FROM goals x 
  JOIN goals y 
    ON y.scorer = x.scorer 
   AND y.goal_id <= x.goal_id 
 GROUP 
    BY x.game,x.goal_id,x.scorer
)n
GROUP BY game;

+------+--------------------------------------+
| game | scorers                              |
+------+--------------------------------------+
|    1 | molly(1),sarah(1),angela(1),molly(2) |
|    2 | jane(1),molly(3),sarah(2)            |
+------+--------------------------------------+

The subquery on its own will give the alternative display