MySQL给每个组一个等级 [英] MySQL give a rank to each group

问题描述

我想做的是用相同的id来更新所有行列，并增加数字，并且当没有更多的id时，我继续下一个.

What I'm trying to do is update all rows column with the same id with increasing number and when there is no more of this id I to go on the next one.

表格:

id  column
1   0
1   0
2   0
2   0
3   0
3   0

我尝试过:

SELECT @i:=0;
UPDATE table SET column = @i:=@i+1

这样，我更新了列，但列值像1 2 3 4 5 6一样 每当行的id更改时，我都必须将@i重置为0. OR:

This way I updated the column but the column values goes like 1 2 3 4 5 6 and I must reset the @i back to 0 everytime when id of the row has changed. OR:

表格:

id  column
1   1
1   2
2   1
2   2
3   1
3   2

我猜这应该是一个循环.但是无法做到.另外，当我尝试在MySQL(PHPMyAdmin)中进行循环时，它给了我一个错误.我读到的是我不能直接在PHPMyAdmin中进行循环，或者我误解了吗?

I guess this should be made by a loop. But can't get it. Plus when I tried to make a loop in the MySQL (PHPMyAdmin), it gave me an error. What I read around is that I can't make loops directly in the PHPMyAdmin or I misunderstood it?

推荐答案

UPDATE table ,(SELECT @curRow := 0, @curCol := '') r SET column=
    ( CASE id WHEN @curCol  THEN @curRow := @curRow + 1  ELSE @curRow := 1 AND @curCol := id END)

测试

这篇关于MySQL给每个组一个等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！