Как реализовать косвенную сортировку в SQL

Очень часто, согласно вашим бизнес-правилам, порядок сортировки не является «естественным», как при числовой или буквенно-цифровой сортировке. Возможно, определено какое-то бизнес-правило, которое GE526OTACCD3ANPH-149839должно быть последним в списке. Или пользователь может переставить названия продуктов на экране с помощью перетаскивания, создав новый порядок сортировки.

Конечно, мы могли бы обсудить, должна ли такая сортировка выполняться на уровне пользовательского интерфейса или нет, но давайте предположим, что бизнес-обоснование или требования к производительности или общая архитектура, необходимые для этой сортировки, должны выполняться в базе данных. Как это сделать? Через…

Сортировка по косвенности

На самом деле, вы хотите сортировать не по названию продукта, а по заранее заданному перечислению таких имен. Другими словами, вам нужна такая функция:

С простым SQL, есть много способов сделать выше. Вот два из них ( также видно из моего ответа переполнения стека ):

Используя выражение CASE

Вы можете легко указать базе данных явное косвенное указание сортировки, используя CASEвыражение в вашем ORDER BYпредложении:

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')
ORDER BY
  CASE WHEN name = 'CE367FAACDHCANPH-151556' THEN 1
       WHEN name = 'CE367FAACEX9ANPH-153877' THEN 2
       WHEN name = 'NI564FAACJSFANPH-162605' THEN 3
       WHEN name = 'GE526OTACCD3ANPH-149839' THEN 4
  END

Обратите внимание, что я использовал CASE WHEN predicate THEN value ENDсинтаксис, потому что он реализован на всех диалектах SQL. В качестве альтернативы ( если вы не используете Apache Derb y), вы также можете сохранить некоторые символы при наборе текста, написав:

ORDER BY
  CASE name WHEN 'CE367FAACDHCANPH-151556' THEN 1
            WHEN 'CE367FAACEX9ANPH-153877' THEN 2
            WHEN 'NI564FAACJSFANPH-162605' THEN 3
            WHEN 'GE526OTACCD3ANPH-149839' THEN 4
  END

Конечно, это требует повторения одинаковых значений в предикате и в косвенной сортировке. Вот почему в некоторых случаях вам может повезти больше …

С помощью INNER JOIN

В следующем примере предикат и косвенность сортировки обрабатываются в простой производной таблице, которая INNER JOINпередается в исходный запрос:

SELECT product.name
FROM product
JOIN (
  VALUES('CE367FAACDHCANPH-151556', 1),
        ('CE367FAACEX9ANPH-153877', 2),
        ('NI564FAACJSFANPH-162605', 3),
        ('GE526OTACCD3ANPH-149839', 4)
) AS sort (name, sort)
ON product.name = sort.name
ORDER BY sort.sort

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

Использование API косвенной сортировки jOOQ

Направленность сортировки немного утомительна, поэтому jOOQ имеет специальный синтаксис для этого варианта использования, который также задокументирован в руководстве . Любой из следующих операторов выполняет то же самое, что и приведенный выше запрос

// jOOQ generates 1, 2, 3, 4 as values in the
// generated CASE expression
DSL.using(configuration)
   .select(PRODUCT.NAME)
   .from(PRODUCT)
   .where(NAME.in(
      "CE367FAACDHCANPH-151556",
      "CE367FAACEX9ANPH-153877",
      "NI564FAACJSFANPH-162605",
      "GE526OTACCD3ANPH-149839"
   ))
   .orderBy(PRODUCT.NAME.sortAsc(
      "CE367FAACDHCANPH-151556",
      "CE367FAACEX9ANPH-153877",
      "NI564FAACJSFANPH-162605",
      "GE526OTACCD3ANPH-149839"
   ))
   .fetch();
 
// You can choose your own indirection values to
// be generated in the CASE expression
   .orderBy(PRODUCT.NAME.sort(
      new HashMap<String, Integer>() {{
        put("CE367FAACDHCANPH-151556", 2);
        put("CE367FAACEX9ANPH-153877", 3);
        put("NI564FAACJSFANPH-162605", 5);
        put("GE526OTACCD3ANPH-149839", 8);
      }}
   ))