Помощь с sql запросом

#mysql #sql

Есть таблица table1:

id  city
1   Москва
2   Воронеж
3   Самара


Есть таблица table2:

id  id_city rating
1   1       22
2   1       66
3   1       95
4   2       12
5   2       37
6   2       13
7   3       68
8   3       54
9   3       27


Связь по полям table1.id = table2.id_city
Примерный алгоритм: Берем 1-ый город и выбираем у него самый большой рейтинг. Затем
берем 2-ой город и выбираем у него самый большой рейтинг. Тоже самое с 3-им. Затем
все повторяется, начинаем с 1-ого города.
Необходимо сделать такой запрос, чтобы результат был следующим:

id_city rating
1       95
2       37
3       68
1       66
2       13
3       54
1       22
2       12
3       27

    

Ответы

Ответ 1

set @pk1 ='';
set @rn1 =1;


SELECT  id_city,
    rating 

FROM
(SELECT  id_city,
    rating,
      @rn1 := if(@pk1=id_city, @rn1+1,1) as rowNumber,
      @pk1 := id_city              
FROM
 (SELECT  id_city,
        rating
FROM    table2
ORDER BY id_city,
    rating desc
) A
) B
order by rownumber, id_city


Решение можно проверить тут http://sqlfiddle.com/#!9/3683a4/5


Ответ 2

Давайте вместе решим вашу задачу поэтапно и заодно разберёмся в устройстве переменных
mySQL и счётчиков.

Для начала подготовим тестовые данные. Вполне сгодится временная таблица. Я её создал так:

create temporary table t(id int, id_city int, rating int)
select 1 id, 1 id_city, 22 rating union
select 2,    1,         66 union
select 3,    1,         95 union
select 4,    2,         12 union
select 5,    2,         37 union
select 6,    2,         13 union
select 7,    3,         68 union
select 8,    3,         54 union
select 9,    3,         27


Посмотреть на данные в ней теперь можно так (они должны совпасть с теми, что вы привели
в самом вопросе):

select id_city, rating
from t
order by id_city, rating desc


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

Вот это самое "ранее" наводит на мысль завести для каждого города свой собственный
счётчик. Пронумеровать записи о городах в порядке убывания рейтинга и только потом
вывести результат, сортируя его по этому счётчику. Чтож, приступим.

В mySQL можно заводить свои собственные переменные. Правила те же, что приняты для
переменных (содержит буквы, цифры, знак подчёркивания и не может начинаться с цифры),
но с префиском - собакой @. Плюс переменные можно сразу же изменять по ходу дела -
каждая строка выборки будет запускать ваши операции над переменными.

select id_city, rating, @rowNum := @rowNum + 1
from t
join (select @rowNum := 0) rowCounter
order by id_city, rating desc


В это запросе join (select .. выполнится только один раз. Так вы получите новую переменную
и сразу присвоите ей значение. Но @rowNum := @rowNum + 1 выполнится для каждой строки
и в итоге вы получите столбик со сквозной нумерацией:

1   95  1
1   66  2
1   22  3
2   37  4
2   13  5
...


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

Очевидно, что идешку города надо запоминать и учитывать в каждой новой строке. Пришла
старая - увеличиваем счётчик. Пришла новая - это новый город и счётчик надо сбросить.
Легко решается простым IF'ом:

select id_city, rating, @rowNum := if(id_city = @prevCityId, @rowNum + 1, 1), @prevCityId
:= id_city
from t
join (select @rowNum := 0) rowCounter
join (select @prevCityId := null) cityCache
order by id_city, rating desc


Очень важно сначала обновить счётчик до нового значения, а только потом запомнить
идешку города текущей строки. Результат (третий столбик - нужным нам счётчик сквозной
для города, но не всего списка):

1   95  1   1
1   66  2   1
1   22  3   1
2   37  1   2
2   13  2   2
...


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

select d.id_city, d.rating
from (
    select id_city, rating, @rowNum := if(id_city = @prevCityId, @rowNum + 1, 1)
as rowNum, @prevCityId := id_city
    from t
    join (select @rowNum := 0) rowCounter
    join (select @prevCityId := null) cityCache
    order by id_city, rating desc
) d
order by rowNum, id_city, rating desc


Оставляем в селекте внешнего запроса только нужные нам поля и получаем результат:

1   95
2   37
3   68
1   66
2   13
3   54
1   22
2   12
3   27


Ответ 3

Идея та же.

   select id_city, rating from (
        SELECT 
        IF(@city=id_city, @i:=@i+1, @i:=1) num,
        @city:=id_city id_city, rating
            FROM Table2, (SELECT @i:=0, @city:=0) X
            ORDER BY id_city, rating desc
    ) X
    order by num, id_city;