mysqlパケットランキングsql文

表を作る

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;