MySQL为每个ID添加行号 [英] MySQL Add rownumbers per ID

问题描述

我有一个带有 sessionID 的表测试.我想对 ID 进行分组，并为每个 ID 的每条记录添加行号.我在 MySql 中使用了 MSSQL Row_Number() over(order by) 和添加了一个 if 语句(按 session_ID 分组).但是，它不起作用.有谁知道为什么这不起作用?

I have a table test with sessionIDs. I want to group the IDs and add rownumbers to each record per ID. I used MSSQL Row_Number() over(order by) in MySql and added an if-statement (to group per session_ID). However, it doesn't work. Does anyone know why this doesn't work?

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  

推荐答案

row_num 后需要一个逗号，并且需要一个 order by:

You need a comma after row_num and you need an 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;

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

You can write this as a single query:

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;

这篇关于MySQL为每个ID添加行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！