Как стать автором
Обновить

Хранимые процедуры и временные таблицы. 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.


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

Теги:
Хабы:
Всего голосов 15: ↑8 и ↓7 +1
Просмотры 4.3K
Комментарии Комментарии 12