mysql变量赋值实现筛选排行榜

-- 表结构

create table rank_user
(
    id   varchar(64) primary key comment '主键',
    name varchar(128) not null comment '名称'
) comment '排行榜用户';

create table point_record
(
    id    varchar(64) primary key comment '主键',
    user_id varchar(64) not null comment '用户id',
    point int default 0 comment '积分'
) comment '积分记录';

-- 造数据
insert into rank_user values ('1341648773400379393','用户0');
insert into point_record values ('1341648773408768001','1341648773400379393', 14);
insert into point_record values ('1341648773408768002','1341648773400379393', 13);
insert into point_record values ('1341648773408768003','1341648773400379393', 4);
insert into point_record values ('1341648773408768004','1341648773400379393', 8);
insert into point_record values ('1341648773408768005','1341648773400379393', 12);
insert into rank_user values ('1341648773408768006','用户1');
insert into point_record values ('1341648773408768007','1341648773408768006', 18);
insert into point_record values ('1341648773408768008','1341648773408768006', 0);
insert into rank_user values ('1341648773408768009','用户2');
insert into point_record values ('1341648773408768010','1341648773408768009', 6);
insert into rank_user values ('1341648773408768011','用户3');
insert into point_record values ('1341648773408768012','1341648773408768011', 13);
insert into point_record values ('1341648773408768013','1341648773408768011', 13);
insert into rank_user values ('1341648773408768014','用户4');
insert into rank_user values ('1341648773408768015','用户5');
insert into point_record values ('1341648773408768016','1341648773408768015', 6);
insert into point_record values ('1341648773408768017','1341648773408768015', 19);
insert into point_record values ('1341648773408768018','1341648773408768015', 10);
insert into point_record values ('1341648773408768019','1341648773408768015', 11);
insert into rank_user values ('1341648773408768020','用户6');
insert into point_record values ('1341648773408768021','1341648773408768020', 3);
insert into point_record values ('1341648773408768022','1341648773408768020', 14);
insert into point_record values ('1341648773408768023','1341648773408768020', 13);
insert into point_record values ('1341648773408768024','1341648773408768020', 0);
insert into point_record values ('1341648773408768025','1341648773408768020', 7);
insert into point_record values ('1341648773408768026','1341648773408768020', 3);
insert into point_record values ('1341648773408768027','1341648773408768020', 1);
insert into rank_user values ('1341648773408768028','用户7');
insert into point_record values ('1341648773408768029','1341648773408768028', 9);
insert into point_record values ('1341648773408768030','1341648773408768028', 1);
insert into point_record values ('1341648773408768031','1341648773408768028', 14);
insert into point_record values ('1341648773408768032','1341648773408768028', 18);
insert into point_record values ('1341648773408768033','1341648773408768028', 4);
insert into point_record values ('1341648773408768034','1341648773408768028', 5);
insert into rank_user values ('1341648773408768035','用户8');
insert into point_record values ('1341648773408768036','1341648773408768035', 0);
insert into point_record values ('1341648773408768037','1341648773408768035', 18);
insert into rank_user values ('1341648773408768038','用户9');
insert into point_record values ('1341648773408768039','1341648773408768038', 9);
insert into rank_user values ('1341648773408768040','用户10');
insert into point_record values ('1341648773408768041','1341648773408768040', 19);
insert into rank_user values ('1341648773408768042','用户11');
insert into point_record values ('1341648773408768043','1341648773408768042', 16);
insert into rank_user values ('1341648773408768044','用户12');
insert into point_record values ('1341648773408768045','1341648773408768044', 15);
insert into point_record values ('1341648773408768046','1341648773408768044', 3);
insert into rank_user values ('1341648773408768047','用户13');
insert into point_record values ('1341648773408768048','1341648773408768047', 6);
insert into point_record values ('1341648773408768049','1341648773408768047', 8);
insert into rank_user values ('1341648773408768050','用户14');

-- 查询一下排行顺序
select * from rank_user a inner join (
    select user_id, ifnull(sum(point),0) as points
    from point_record
    group by user_id
) b on a.id = b.user_id
order by b.points desc;

-- 查询第11，12，13名的用户数据和总积分
select x.*
from (
         select @rank := @rank + 1, u.id, u.name, r.points
         from rank_user u
                  inner join (
             select user_id, sum(point) as points, @rank := 0
             from point_record
             group by user_id
         ) r on u.id = r.user_id
         order by r.points desc
     ) x
limit 10,3