mysql 更新排名,mysql分組排名（更新篇）

排名並更新#########################################################

USE test_db;

建表

CREATE TABLE t_rank_update

(

pid INT(4) PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20) NOT NULL,

age INT(3),

rank INT,

groupid INT

)

插入

INSERT INTO t_rank_update(NAME, age, rank,groupid)

VALUES

(‘Peter’, 19,0 , 1),

(‘Andre’,20,0 ,1),

(‘Vino’,20,0 ,3),

(‘John’,25, 0,3),

(‘Tom’,24,0 ,4),

(‘Brian’,21,0 ,4),

(‘Andy’,22, 0,3),

(‘George’,23, 0,4),

(‘Dew’,23, 0,4),

(‘Kris’,25, 0,4),

(‘Samual’,25,0,4),

(‘William’,26,0,2);

更新，相同數值系統自動排名(不分組)

SET @rank:=0;

UPDATE t_rank_update,

(

SELECT pid, @rank := @rank + 1 AS ranknum

FROM t_rank_update

ORDER BY age DESC

) tmp

SET t_rank_update.rank=tmp.ranknum

WHERE t_rank_update.pid=tmp.pid

更新，相同數值相同排名(不分組)

SET @rank:=0;

SET @preAge:=0;

UPDATE t_rank_update,

(

SELECT pid, (IF(@preAge <> age, @rank :=@rank + 1, @rank)) ranknum ,@preAge:=age

FROM t_rank_update

ORDER BY age

) tmp

SET t_rank_update.rank=tmp.ranknum

WHERE t_rank_update.pid=tmp.pid

相同數值系統自動排名(分組)

SET @preAge:=0;

SET @row:=0;

SET @curGroup:=0;

UPDATE t_rank_update a

INNER JOIN

(

SELECT pid,NAME,age, groupid,

CASE

WHEN @curGroup=groupid THEN @row := @row + 1

WHEN @curGroup<>groupid THEN @row:=1

END urank,

@curGroup:=groupid,

@preAge:=age

FROM t_rank_update

ORDER BY groupid DESC, age DESC

) b ON a.pid=b.pid

SET a.rank=b.urank;

相同數值相同排名(分組)

SET @preAge:=0;

SET @row:=0;

SET @curGroup:=0;

UPDATE t_rank_update a

INNER JOIN

(

SELECT pid,NAME,age, groupid,

CASE

WHEN @curGroup=groupid THEN @row := IF(@preAge = age, @row, @row := @row + 1)

WHEN @curGroup<>groupid THEN @row:=1

END urank,

@curGroup:=groupid,

@preAge:=age

FROM t_rank_update

ORDER BY groupid DESC, age DESC

) b ON a.pid=b.pid

SET a.rank=b.urank;