mysql分组排名sql语句

Myth丶恋晨 2023-06-27 12:47 2阅读 0赞

建表

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;