#mysql
#mysql
Вопрос:
У меня этот запрос выполняется на двух разных скриптовых сайтах, оба настроены на использование MySQL 5.6:
SELECT name, rank, position FROM(
SELECT name, position,
@rank:= IF(@prev = name, @rank 1, 1) AS rank,
@prev:= name
FROM (SELECT * FROM drivers
LEFT JOIN results on drivers.id = results.driver_id
JOIN (SELECT @rank := 1) AS init
ORDER BY name, results.position ASC) AS temp
) AS derived WHERE rank <= 3 ORDER BY name, rank
Предполагается, что это дает 3 верхние позиции каждого драйвера. Запрос работает на скрипте # 1, но не на скрипте # 2 или производственном сервере, хотя все три из них работают на MySQL 5.6.
Есть ли настройка, которую я упускаю?
Комментарии:
1. В Oracle вычислять что-либо вручную в select подобным образом было бы очень опасно, поскольку результаты не определены и могут зависеть от того, как оптимизатор решит выполнить запрос. Я бы предположил, что то же самое относится и к MySQL, и что разница, которую вы получаете, заключается в том, что системы генерируют разные планы? Или даже то, что они используют одни и те же планы, но сталкиваются с данными в другом порядке. Вы не можете использовать встроенную функцию RANK, или это было добавлено позже?
2. @ewramner Похоже, что проблема не была инициализирована
prev
должным образом. К сожалению,RANK
был добавлен в 8.0
Ответ №1:
Вы можете использовать следующее решение:
SELECT name, rank, position FROM (
SELECT name, position,
@rank:= IF(@prev = name, @rank 1, 1) AS rank,
@prev:= name
FROM (
SELECT *
FROM drivers LEFT JOIN results ON drivers.id = results.driver_id
JOIN (SELECT @rank := 1) AS init_rank
JOIN (SELECT @prev := '') AS init_prev
ORDER BY name, results.position ASC
) AS temp
) AS derived
WHERE rank <= 3
ORDER BY name, rank
Я также добавил инициализацию для @prev
переменной на JOIN
.
разные демоверсии:
Начиная с MySQL 8.0, вы можете использовать встроенную RANK
функцию window. Таким образом, вам не нужны переменные @prev
or @rank
:
SELECT name, `rank`, position FROM (
SELECT name, position, RANK() OVER (PARTITION BY name ORDER BY name, position) AS `rank`
FROM drivers LEFT JOIN results ON drivers.id = results.driver_id
) AS derived
WHERE `rank` <= 3
ORDER BY name, `rank`
Комментарии:
1. Это отлично, спасибо. Не могли бы вы объяснить, почему это работает? Я бы ожидал, что
@prev
он будет «инициализирован» после первой итерации, когдаname
будет назначен, что приведетtrue
к последующим итерациям вIF
условии. Тем не менее, кажется, что это всегдаfalse
.