aRSWb8pm

1
2delimiter ;
3
4drop table if exists users;
5
6create table users
7(
8user_id int not null auto_increment primary key,
9points int not null default 0
10);
11
12insert into users (points) values (10);
13insert into users (points) values (15);
14insert into users (points) values (3);
15insert into users (points) values (8);
16insert into users (points) values (16);
17insert into users (points) values (22);
18insert into users (points) values (10);
19insert into users (points) values (9);
20insert into users (points) values (6);
21insert into users (points) values (30);
22
23select * from users;
24
25
26drop procedure if exists get_user_rank;
27
28delimiter #
29
30create procedure get_user_rank
31(
32in p_user_id int
33)
34
35begin
36
37set @rank = 0;
38
39select
40 u.user_id,
41 r.rank,
42 u.points
43from
44 users u
45inner join
46(
47select
48 @rank:=@rank+1 as rank,
49 user_id,
50 points
51from
52 users
53order by 
54 points desc, user_id
55) r on u.user_id = r.user_id
56where
57 u.user_id = p_user_id;
58
59	
60end #
61
62delimiter ;
63
64drop procedure if exists get_user_ranks;
65
66delimiter #
67
68create procedure get_user_ranks()
69
70begin
71
72set @rank = 0;
73
74select
75 @rank:=@rank+1 as rank,
76 user_id,
77 points
78from
79 users
80order by 
81 points desc, user_id;
82	
83end #
84
85delimiter ;
86
87call get_user_rank(1);
88call get_user_ranks();