Stored procedures and temporary tables. MySQL for beginners

I do not know how anyone, but I, if I find a simple solution to a problem, there is always a desire to share this decision with someone. So today, having solved the task, I would like to share its solution.

The essence of the problem is simple: there is a certain user rating. It is necessary to show the user his current position, as well as the position of his closest competitors. Simply put, show N-5..N + 5 positions in the ranking, where N is the user's position in the ranking.

The user rating table contains four fields: id , points , time , uid and answered . We will be interested only in two fields: points and time on which the rating is built.

Obviously, one request is not enough, so we will immediately create a stored procedure get_user_rating (user_id), which will receive a user rating.
')

drop procedure if exists get_user_rating // create procedure get_user_rating ( in uid int unsigned) end // * This source code was highlighted with Source Code Highlighter .

1. drop procedure if exists get_user_rating // create procedure get_user_rating ( in uid int unsigned) end // * This source code was highlighted with Source Code Highlighter .
2. drop procedure if exists get_user_rating // create procedure get_user_rating ( in uid int unsigned) end // * This source code was highlighted with Source Code Highlighter .
3. drop procedure if exists get_user_rating // create procedure get_user_rating ( in uid int unsigned) end // * This source code was highlighted with Source Code Highlighter .

drop procedure if exists get_user_rating // create procedure get_user_rating ( in uid int unsigned) end // * This source code was highlighted with Source Code Highlighter .

Since we need to return the result from the procedure, namely a sample consisting of [6..11] rows, we will use a temporary table created by the procedure call to store it. By the way, we can get the result of the query executed in the body of the stored procedure in another way, but we will not touch it now.

1. drop procedure if exists get_user_rating //
2. create procedure get_user_rating ( in uid int unsigned)
3. begin
4. drop table if exists rating_tmp;
5. create temporary table rating_tmp (
6. `points` int unsigned,
7. ` time` int unsigned,
8. `uid` int unsigned,
9. `answered int unsigned
10. );
11. end //

* This source code was highlighted with Source Code Highlighter .

Next, we select user points:

1. select @points: = points from rating r where r.uid = uid;

* This source code was highlighted with Source Code Highlighter .

Now, using the value obtained above, we select the nearby 5 lines with a larger and 5 lines with a smaller value of points.

1. select h.points, h. time , h.uid, h.answered
2. from rating h
3. where @points <h.points
4. order by h.points desc , h. time desc
5. limit 5

* This source code was highlighted with Source Code Highlighter .

and

1. select b.points b. time , b.uid, b.answered
2. from rating b
3. where @points> points
4. order by b.points desc , b. time desc
5. limit 5)

* This source code was highlighted with Source Code Highlighter .

We combine all three results and insert them into our temporary table. Get the resulting stored procedure:

1. drop procedure if exists get_user_rating //
2. create procedure get_user_rating ( in uid int unsigned)
3. begin
4. drop table if exists rating_tmp;
5. create temporary table rating_tmp (
6. `points` int unsigned,
7. ` time` int unsigned,
8. `uid` int unsigned,
9. `answered int unsigned
10. );
12. select @points: = points from rating r where q.uid = uid;
14. insert into rating_tmp (points, `time`, uid, answered)
15. ( select h.points, h. time , h.uid, h.answered
16. from rating h
17. where @points <h.points
18. order by h.points desc , h. time desc
19. limit 5)
21. union
22. ( select points, `time`, uid, answered from r1 rating where r1.uid = uid)
23. union
25. ( select b.points, b. time , b.uid, b.answered
26. from rating b
27. where @points> points
28. order by b.points desc , b. time desc
29. limit 5)
31. order by points desc , ` time` desc ;
32. end //

* This source code was highlighted with Source Code Highlighter .

The main thing is not to forget to sort the result of the union. Now in the application we get our rating:

1. call get_user_rating (1);
3. select * from rating_tmp;

* This source code was highlighted with Source Code Highlighter .

Is done. I hope this information will be useful.