MySQL 添加每个 ID 的行号

Question

我有一个带有 sessionID 的表测试。我想对 ID 进行分组，并为每个 ID 的每条记录添加行号。我用过MSSQL Row_Number() over(order by) in MySql并添加了一个 if 语句(按 session_ID 进行分组)。然而，这不起作用。有谁知道为什么这不起作用？

SET @row:=0
SET @sessionID:=0

SELECT 
    session_ID
    @row:=if(@sessionID = session_ID, @row+1, 1) AS row_num
    @sessionID := session_ID
FROM test  

Answer 1

您需要在 row_num 之后添加一个逗号，并且需要一个 order by:

SET @row:=0;
SET @sessionID:=0;

SELECT 
    session_ID,
    @row:=if(@sessionID = session_ID, @row+1, 1) AS row_num,
    @sessionID := session_ID
FROM test
ORDER BY session_ID;

您可以将其编写为单个查询:

SELECT session_ID,
       @row:=if(@sessionID = session_ID, @row+1, 1) AS row_num,
       @sessionID := session_ID
FROM test cross join
     (select @row := 0, @sessionID := 0)
ORDER BY session_ID;