Есть таблица 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
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
Идея та же.
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;
Давайте вместе решим вашу задачу поэтапно и заодно разберёмся в устройстве переменных 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
Кофе для программистов: как напиток влияет на продуктивность кодеров?
Рекламные вывески: как привлечь внимание и увеличить продажи
Стратегії та тренди в SMM - Технології, що формують майбутнє сьогодні
Выделенный сервер, что это, для чего нужен и какие характеристики важны?
Современные решения для бизнеса: как облачные и виртуальные технологии меняют рынок
Как выбрать строки с помощью оператора IN именно в том порядке, в котором они переданы? Те
Здравствуйте! Запускаю backend проекта на Node Express, Angular и получаю ошибку: Error: Cannot enqueue Query after fatal error
Написание методов доступа чтения и записи в list sharepoint без использования его самогоМожет быть есть эмуляция или api для эмуляции?