Хранимые процедуры и временные таблицы. MySQL для начинающих

Не знаю как кто, а я, если нахожу простое решение к какой-либо задаче, то всегда возникает желание этим решением с кем-нибудь поделиться. Так и сегодня, решив поставленную задачу, хотел бы поделиться ее решением.

Суть задачи проста: есть некий рейтинг пользователей. Необходимо показать пользователю его текущее положение, а также положение его ближайших конкурентов. Проще говоря, показать N-5..N+5 позиций в рейтинге, где N положение пользователя в рейтинге.

Таблица с рейтингами пользователей содержит четыре поля: id, points, time, uid и answered. Нас будут интересовать только два поля: points и time по которым и строится рейтинг.

Очевидно, что одним запросом здесь не обойтись, поэтому сразу создадим хранимую процедуру get_user_rating(user_id), которая будет получать рейтинг пользователя.

1. drop procedure if exists get_user_rating //
2. create procedure get_user_rating (in uid int unsigned)
3. end //

* This source code was highlighted with Source Code Highlighter.

Так как нам понадобиться возвращать результат из процедуры, а именно выборку, состоящую из [6..11] строк, то для ее хранения будем использовать временную таблицу, создаваемую при вызове процедуры. К слову сказать, получить результат запроса, выполненного в теле хранимой процедуры, можно и другим способом, но мы не будем его сейчас затрагивать.

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.

Далее мы выбираем баллы пользователя:

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

* This source code was highlighted with Source Code Highlighter.

Теперь, используя полученное выше значение, выбираем близлежащие 5 строк с большим и 5 строк с меньшим значением баллов.

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.

и

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.

Объединим все три результата и вставим в нашу временную таблицу. Получим результирующую хранимую процедуру:

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.  
12.  select @points := points from rating r where q.uid = uid;
13.  
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)
20.  
21.      union
22.     (select points, `time`, uid, answered from rating r1 where r1.uid = uid)
23.      union
24.  
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)
30.  
31.     order by points desc, `time` desc;
32. end //

* This source code was highlighted with Source Code Highlighter.

Главное не забыть отсортировать результат объединения. Теперь в приложении получаем наш рейтинг:

1. call get_user_rating(1);
2.  
3. seleсt * from rating_tmp;

* This source code was highlighted with Source Code Highlighter.

Готово. Надеюсь данная информация окажется полезной.