Обратное соединение в MySQL

SO,

Проблема

Мой вопрос о том, как объединить таблицу в MySQL с собой в обратном порядке? Предположим, что у меня есть:

id  name
1   First
2   Second
5   Third
6   Fourth
7   Fifth
8   Sixth
9   Seventh
13  Eight
14  Nine
15  Tenth

- и теперь я хочу создать запрос, который вернет объединенные записи в обратном порядке:

left_id name    right_id    name 
   1    First      15   Tenth 
   2    Second     14   Nine 
   5    Third      13   Eight 
   6    Fourth      9   Seventh 
   7    Fifth       8   Sixth 
   8    Sixth       7   Fifth 
   9    Seventh     6   Fourth 
  13    Eight       5   Third 
  14    Nine        2   Second 
  15    Tenth       1   First

Мой подход

У меня есть этот запрос:

SELECT 
  l.id AS left_id, 
  l.name, 
  (SELECT COUNT(1) FROM sequences WHERE id<=left_id) AS left_order, 
  r.id AS right_id,
  r.name,
  (SELECT COUNT(1) FROM sequences WHERE id<=right_id) AS right_order 
FROM 
  sequences AS l 
  LEFT JOIN 
    sequences AS r ON 1
HAVING
  left_order+right_order=(1+(SELECT COUNT(1) FROM sequences));

-это этот fiddle для структуры и кода образца.

Некоторые фон

Для этого нет смысла. Раньше я делал это в приложении. Теперь в основном любопытство, если есть способ сделать это в SQL - почему я ищу не просто "какое-либо решение" (например, мое), но и как можно более простое решение. Исходная таблица всегда будет небольшой (< 10.000 записей) - так что производительность не вещь, чтобы заботиться, я думаю.

Вопрос

Можно ли как-нибудь упростить мой запрос? Кроме того, важно не использовать переменные. Заказ может быть включен в результат (как в моей скрипке) - но это не обязательно.

Ответы

Ответ 1

Единственное, что я могу подумать об улучшении, - это

SELECT 
  l.id AS left_id, 
  l.name ln, 
  (SELECT COUNT(1) FROM sequences WHERE id<=left_id) AS left_order, 
  r.id AS right_id,
  r.name rn,
  (SELECT COUNT(1) FROM sequences WHERE id>=right_id) AS right_order 
FROM 
  sequences AS l 
  LEFT JOIN 
    sequences AS r ON 1
HAVING
  left_order=right_order;

Есть 2 изменения, которые должны сделать это немного быстрее:

1) Вычисление правильного порядка в обратном порядке в первую очередь

2) не используйте SELECT COUNT в последней строке.

Изменить: я наложил ln, rn, потому что я не мог видеть столбцы в скрипте

Ответ 2

Без SQL-стандарта RANK() OVER (...) вы должны сами вычислить заказ, как только обнаружили.

RANK() строки - это просто 1 + COUNT() всех строк с лучшим рангом. (DENSE_RANK() для сравнения - 1 + COUNT() всех лучших DISTINCT рангов.) Хотя RANK() может быть вычислен как скалярный подзапрос в вашей проекции SELECT, как, например, вы сделали с SELECT (SELECT COUNT(1) ...), ... - Я предпочитаю присоединяться:

    SELECT lft.id AS "left_id", lft.name AS "left_name",
           rgt.id AS "right_id", rgt.name AS "right_name"
      FROM (   SELECT s.id, s.name, COUNT(1) AS "rank"     -- Left ranking
                 FROM sequences s
            LEFT JOIN sequences d ON s.id <= d.id
             GROUP BY 1, 2) lft
INNER JOIN (   SELECT s.id, s.name, COUNT(1) AS "rank"     -- Right ranking
                 FROM sequences s
            LEFT JOIN sequences d ON s.id >= d.id
             GROUP BY 1, 2) rgt
           ON lft.rank = rgt.rank
  ORDER BY lft.id ASC;

Ответ 3

SET @rank1=0;
SET @rank2=0;

SELECT *
FROM (SELECT *, @rank1 := @rank1 + 1 AS row_number FROM sequences ORDER BY ID ASC) t1
INNER JOIN (SELECT *, @rank2 := @rank2 + 1 AS row_number FROM sequences ORDER BY ID DESC) t2
on t1.row_number = t2.row_number

По какой-то причине sql fiddler показывает только 3 столбца для этого, не уверен, что мой запрос плох.