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


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

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 12

      +4
      хм, не сочтите критикой, поидее подобную задачу можно решить одним подобным запросом

      SELECT * FROM (
      (SELECT a2.* FROM
      (SELECT * FROM t1 where name = «name1») a1, t1 as a2
      WHERE a2.rait < a1.rait ORDER BY a2.rait DESC LIMIT 2 )
      UNION (
      SELECT a2.* FROM
      (SELECT * FROM t1 where name = «name1») a1, t1 as a2
      WHERE a2.rait > a1.rait ORDER BY a2.rait ASC LIMIT 2 )
      UNION
      (SELECT * FROM t1 where name = «name1»)
      ) as itog
      ORDER BY itog.rait DESC

      В данном случае есть таблица с двумя полями name и rait
      запрос выдает в 5 строчек (2 с рейтингом больше, 1 строчка — искомый пользователь, 2 строчки с рейтингом меньше)

        +2
        Критика всегда приветствуется, если она конструктивная. Мне Ваш вариант понравился. Выглядит не так очевидно, но работает, шустрее моего.
        +1
        Немного видоизменил, получилось следующее:
        select * from (

         (select * from rating where points > (select points from rating where uid = 7808) order by points desc limit 5)
            union
         (select * from rating where points < (select points from rating where uid = 7808) order by points desc limit 5)
            union
         (select * from rating where uid = 7808)

         ) rating
        order by rating.points desc;


        * This source code was highlighted with Source Code Highlighter.
          +2
          На троечку. Оба не очень понимаете что пишете, т.к. не учитываете вариант, когда разные пользователи набрали одинаковое количество очков. В одном select должно быть нестрогое неравенство и исключение самого пользователя из выборки; либо дополнительно сортировать по уникальному полю: id, login.
            +2
            Ну мне кажется автор не задачу тут показывает как решить, а больше рассказывает о хранимых процедурах. А показать это проще всего на несложном и понятном для всех примере. Я не прав?
              0
              а что помешало автору селект (seleсt * from rating_tmp;) вставить в саму ХП?
                0
                В этом случае пропал бы смысл создавать временную таблицу.
                  0
                  да вы что?

                  а с вынесением этого селекта из процедуры пропадает смысл во всей процедуре.
                0
                Мне почему то кажется, что для обучения начинающих категорически противопоказано приводить примеры кода, на которые можно сразу вешать вывеску "!!! Никогда не делайте так !!!!"

                Зачем нужно создавать временную таблицу?
                Что бы количество кода и время выполнения увеличить?
                :-)
                Других причин я не вижу.

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

                Зачем хранить результат?

                Какой другой способ?

                Есть ощущение, что автор абсолютно не понимает как работают хранимые процедуры как они возвращают результат(ы).
                  0
                  Да, отлично!
                  А теперь расскажи нам как хранимые процедуры должны возвращать (сложные и/или объемные) результаты.
                    0
                    Убедительная просьба — почитайте плиз мануал.
                    0
                    Вообще-то я писал, что результат запроса из тела хранимой процедуры можно получить другим способом. Я лишь рассмотрел вариант с временной таблицей.

                    Я не буду отрицать, что я новичок в MySQL. Уверен, что здесь есть такие же люди. Истина рождается в споре, так что лучше выносить разноплановые вопросы на обсуждение, тогда и процесс обучения и получения знаний будет происходить намного быстрее.

                    К примеру, @Horus20 подсказал свой вариант я из него кое-что извлек, за что ему спасибо.

                  Only users with full accounts can post comments. Log in, please.