Оцените результаты MySQL после объединения с другой таблицей, также отфильтровав их по условию

Question

Я нашел способ ранжирования результатов, но еще не нашел решения, как их ранжировать после того, как они были отфильтрованы по условию. Что мне нужно сделать, это:

1) Сортировка баллов в порядке убывания
2) объединить результаты с таблицей счетов
3) Отфильтруйте результаты по возрасту и полу (из таблицы счетов)
4) Оцените их
5) Получите результаты в диапазоне рангов (IE: 25-75, но для простоты я поставил 0-10)

Каждое найденное мной решение либо неправильно оценивает их, либо ранжирует их, а затем фильтрует результаты. Тем не менее, это оставляет меня с рангами 1, 3, 4, 6, 10 и т. Д., Когда я хочу 1, 2, 3, 4, 5 и т. Д. Или 25, 26, 27, 28, 29 и т. Д.

Вот что у меня сейчас есть:

SELECT a.id, a.username, a.country, score, user_id, duration, rank
FROM (
    SELECT s.*, a.age, a.gender, @rownum := @rownum + 1 AS rank
    FROM scores s,
        (SELECT @rownum := 0) r
    LEFT JOIN accounts a ON 1
    WHERE a.age>=18 && a.age<=35 && a.gender='m'
    ORDER BY score DESC, duration DESC, time ) `selection`
LEFT JOIN accounts a ON a.id=user_id
WHERE rank >= 0 && rank <= 10 && a.age>=18 && a.age<=35 && a.gender='m'

Проблема с приведенным выше запросом состоит в том, что он дает мне правильное ранжирование без каких-либо пропусков, но оценки совершенно не в порядке. IE. 5, 1, 10, 7, 125, 50 и т. Д. IE. Они выходят в том же порядке, что и в базе данных, не отсортированы.

Вот как выглядел мой предыдущий запрос:

SELECT a.id, a.username, a.country, score, duration, rank
FROM (
    SELECT s.*, @rownum := @rownum + 1 AS rank
    FROM scores s,
        (SELECT @rownum := 0) r
    ORDER BY score DESC, duration DESC, time ) `selection`
LEFT JOIN accounts a ON a.id=user_id
WHERE rank >= 0 && rank <= 10 && a.age>=18 && a.age<=35 && a.gender='m'

Этот запрос дает мне правильно отсортированные оценки, но с оценками 3, 5, 8, 9, 11 вместо 1, 2, 3, 4, 5.

Вот некоторые примеры реальных данных...
Ради времени, id, имя пользователя, страна, все опущены, потому что они не имеют отношения к данным и просто повторяются до конца таблицы. Возраст до конца равен "34", как показывает условие, страна и имя пользователя всегда одинаковы, так как этот конкретный пользователь является единственным в таблице с возрастом 34 года.

Первый запрос сверху с правильными рейтингами, но неправильно отсортированными баллами (т.е. в том же порядке, в каком они были добавлены в базу данных):

  age | score | rank
--------------------
  34  |   5   |  1
  34  |   1   |  2
  34  |  22   |  3
  34  |  13   |  4
  34  |  23   |  5
  34  |  23   |  6
  34  |  34   |  7
  34  |  32   |  8
  34  |  58   |  9
  34  |  76   |  10

Второй запрос сверху, правильно отсортированный, но ранги по-прежнему учитывают других пользователей при ранжировании, а затем исключаются в конечном результате:

  age | score | rank
---------------------
  34  |  76   |  3  --- This should have started at 1
  34  |  62   |  4
  34  |  58   |  5
  34  |  42   |  7  --- Notice 6 was skipped
  34  |  34   |  8
  34  |  32   |  9
  34  |  29   |  10

Что я ожидаю:

  age | score | rank
--------------------
  34  |  76   |  1
  34  |  62   |  2
  34  |  58   |  3
  34  |  42   |  4
  34  |  34   |  5
  34  |  32   |  6
  34  |  29   |  7

Answer 1

SELECT a.id, a.username, a.country, score, user_id, duration, rank
FROM (
    SELECT s.*, a.age, a.gender, @rownum := @rownum + 1 AS rank
    FROM scores s,
        (SELECT @rownum := 0) r
    LEFT JOIN accounts a ON 1
    WHERE a.age>=18 && a.age<=35 && a.gender='m'
    ORDER BY score DESC, duration DESC, time ) `selection`
LEFT JOIN accounts a ON a.id=user_id
WHERE rank >= 0 && rank <= 10 && a.age>=18

Answer 2

Я могу предложить следующий запрос на основе вашего образца данных:

SET @rank = 0;
SET @score = NULL;

SELECT
    age,
    @rank:=CASE WHEN @score = score THEN @rank ELSE @rank + 1 END AS rank,
    @score:=score AS score
FROM scores
ORDER BY rank;

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

Выход:

Демо здесь:

Rextester