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

261
27 ноября 2017, 16:11

Есть таблица 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
Answer 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

Answer 2

Идея та же.

   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;
Answer 3

Давайте вместе решим вашу задачу поэтапно и заодно разберёмся в устройстве переменных 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
READ ALSO
MySQL: сортировка выборки в порядке, заданном в операторе IN

MySQL: сортировка выборки в порядке, заданном в операторе IN

Как выбрать строки с помощью оператора IN именно в том порядке, в котором они переданы? Те

279
Node.js MySQL Error: Cannot enqueue Query after fatal error

Node.js MySQL Error: Cannot enqueue Query after fatal error

Здравствуйте! Запускаю backend проекта на Node Express, Angular и получаю ошибку: Error: Cannot enqueue Query after fatal error

442
Резко возрастает iops на linux сервере с mysql и ssd

Резко возрастает iops на linux сервере с mysql и ssd

Сразу скажу, я еще нуб в базах

282
Sharepoint 2013 тестирование чтения и записи offline

Sharepoint 2013 тестирование чтения и записи offline

Написание методов доступа чтения и записи в list sharepoint без использования его самогоМожет быть есть эмуляция или api для эмуляции?

205