MYSQL:使用团队中的顺序更新表

Question

我需要MYSQL方面的帮助。我有一张表格，上面会有多个团队中每个成员的所有比赛终点位置。我希望能够更新一个专栏，告诉我团队成员在团队中的职位。如果需要，它可以只是SQL或存储过程。我正在使用phpmyadmin。

因此，让我们假设以下数据(示例(。在整个表格中，我有20支车队的大约200名车手，但举个例子，我只列出几个。

| Team ID | MemberID | Position | PositionInTeam |
| --------| -------  |----------|----------------|
| 111     | 123123   | 12       |                |
| 112     | 456456   | 51       |                |
| 113     | 789789   | 44       |                |
| 112     | 321321   | 42       |                |
| 111     | 654654   | 68       |                |
| 113     | 987987   | 14       |                |
| 112     | 741741   | 28       |                |

我想运行一个脚本，更新表格，显示骑手在团队中的位置(填写最后一列(

| Team ID | MemberID | Position | PositionInTeam |
| --------| ---------|----------|----------------|
| 111     | 123123   | 12       | 1              |
| 112     | 456456   | 51       | 3              |
| 113     | 789789   | 44       | 2              |
| 112     | 321321   | 42       | 2              |
| 111     | 654654   | 68       | 2              |
| 113     | 987987   | 14       | 1              |
| 112     | 741741   | 28       | 1              |

谢谢你的帮助！

Answer 1

如果您的MySql版本是8.0+，您可以使用ROW_NUMBER()窗口函数来计算每个团队中每个成员的位置并加入表格：

UPDATE tablename t
INNER JOIN (
SELECT *, ROW_NUMBER() OVER (PARTITION BY TeamID ORDER BY Position) rn
FROM tablename
) tt ON tt.TeamID = t.TeamID AND tt.MemberID = t.MemberID
SET t.PositionInTeam = tt.rn

对于以前的版本，使用自联接：

UPDATE tablename t
INNER JOIN (
SELECT t1.TeamID, t1.MemberID, COUNT(*) rn
FROM tablename t1 INNER JOIN tablename t2
ON t2.TeamID = t1.TeamID AND t2.Position <= t1.Position
GROUP BY t1.TeamID, t1.MemberID
) tt ON tt.TeamID = t.TeamID AND tt.MemberID = t.MemberID
SET t.PositionInTeam = tt.rn

请参阅演示