X

MySQL: id в обратном порядке, сохраняя порядок значений других столбцов

Изучая вопросы на Toster-ре, наткнулся на один интересный вопрос, а именно: Как вывести id в обратном порядке, сохраняя при этом порядок значений..

Имеем:

id    colors
--    --------
1     зеленый
2     красный
3     синий
4     белый

Нужно написать запрос, чтобы получить такой вывод

id    colors
--    --------
4     зеленый
3     красный
2     синий
1     белый

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

Я уверен, что Вам лень писать запросы для создания схемы базы, поэтому предоставлю Вам сразу схему создания базы и тестовые данные.

CREATE TABLE tmp (
  id INT,
  color VARCHAR(255)
);

INSERT INTO tmp 
  (id, color)
VALUES 
  (1, 'зеленый'),
  (2, 'красный'),
  (3, 'синий'),
  (4, 'белый')
;

Теперь Вы можете открыть sqlfiddle.com и сразу попробовать написать запрос.

Те кто не сталкивался с sqlfiddle.com ранее, рассказываю как работать: вставляете код выше в поле Schema Panel (слева) и нажимаете кнопку [Build Schema]. Затем в правой части можете писать запрос, опираясь на указанную схему. Например в нашем случае, можно начать с запроса: SELECT * FROM tmp

Так же чтобы исключить обсуждения того, что такие запросы нельзя писать, дополню вопрос тем, что id в данном примере не Primary Key, не имеет UNIQUE индекса и может содержать другие последовательности, например не 1-2-3-4, а 2-5-8-0. Суть вопроса в том, чтобы одним запросом вывести эти значения в обратном порядке, при этом сохраняя последовательность значений во второй колонке.

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

← Назад к первой странице

Мое решение - использовать дополнительный счетчик строк для того, чтобы иметь идентификатор строк, по которому можно соединить результаты двух подзапросов

SELECT id, color FROM (
  SELECT 
    id,
    @join_pk_1:=@join_pk_1 + 1 as join_pk
  FROM tmp, (select @join_pk_1:=0) as join_pk_1
  ORDER BY id DESC
) as tbl1
LEFT JOIN (
  SELECT 
    color,
    @join_pk_2:=@join_pk_2 + 1 as join_pk
  FROM tmp, (select @join_pk_2:=0) as join_pk_2
) as tbl2
ON tbl1.join_pk = tbl2.join_pk

Думаю, это отличная задача для проверки знаний MySQL во время собеседования, кроме того тут есть прямо ооочень много моментов про которые можно поговорить далее: стоит ли так вообще делать, особенно в случае если id это Primary Key;  работа JOIN-ов,  подзапросы; переменные внутри запросов. В общем, лично мне, вопрос очень понравился.

--[добавлено 08.10.18]--

Вернувшись к этому вопросу позже, заметил одну проблему, из-за сортировки по id, запрос неккоректно отработает на наборе данных где id идут не в порядке увеличения, пример

INSERT INTO tmp 
  (id, color)
VALUES 
  (1, 'green'),
  (21, 'red'),
  (3, 'blue'),
  (4, 'white')
;

текущее решение выведет такой результат

id color
---------------
21 green
4 red
3 blue
1 white

вместо ожидаемого

id color
---------------
4 green
3 red
21 blue
1 white

Для решения это проблемы, надо сортировку ORDER BY id DESC заменить на DESC сортировку по номеру строки, тогда запрос будет работать на любом наборе данных.