在SELECT语句中重置SQL变量

Question

我想用一个UPDATE/SELECT语句使用计数器变量@row对桥表上的某些行进行编号。例如：

UPDATE teamrank JOIN (SELECT @row := @row + 1 AS position, name FROM members) 
     USING(teamID, memberID) SET rank = position 

是这样的可能或我需要创建一个游标吗？如果有帮助，我使用的是MySQL 5

Answer 1

- 总是初始化用户变量，它们默认否则为NULL。

SET @prevID:[email protected]:=0; 

UPDATE teamrank t -- one row per team? 
    JOIN members m USING (teamID) -- multiple rows per team? 
    SET rank = 
IF(@prevID != m.teamID, /* Capture when a teamIDs changes */ 
    (@runSum := m.rank) + ((@prevID := m.teamID)*0), /* reset both @runSum and @prevTeam */ 
    (@runSum := @runSum + m.rank) /* increment running sum */ 
) 
-- It is important to have proper sequencing, so to reset @runSum when a teamID changes. 
ORDER BY t.teamID 
; 

我做出了一个假设，即执行团队排名所需的逻辑是团队内个人的排名总和。

同样的技术允许执行任何类型的运行总和或计数器，当“组”更改时需要重置。

- J Jorgenson -

Answer 2

如果我正确地理解你的（不完全）查询，这应该帮助：

UPDATE teamrank 
    JOIN (
     SELECT @row := @row + 1 AS position, name 
     FROM members, 
      (SELECT @row := 0) AS ObligatoryAlias 
    ) USING(teamID, memberID) 
SET rank = position;