Статьи

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

Март 13, 2019

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

Они написали

1
2
3
4
5
6
SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')

У них есть

1
2
3
4
CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
GE526OTACCD3ANPH-149839
NI564FAACJSFANPH-162605

Они хотели

1
2
3
4
CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
NI564FAACJSFANPH-162605
GE526OTACCD3ANPH-149839

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

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

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

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

1
2
3
4
CE367FAACDHCANPH-151556 -> 1
CE367FAACEX9ANPH-153877 -> 2
NI564FAACJSFANPH-162605 -> 3
GE526OTACCD3ANPH-149839 -> 4

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

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

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

01
02
03
04
05
06
07
08
09
10
11
12
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), вы также можете сохранить некоторые символы при наборе текста, написав:

1
2
3
4
5
6
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 ‘связана с исходным запросом:

01
02
03
04
05
06
07
08
09
10
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 имеет специальный синтаксис для этого варианта использования, который также задокументирован в руководстве . Любой из следующих операторов выполняет то же самое, что и приведенный выше запрос:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
// 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);
      }}
   ))

Вывод

Переадресация сортировки — это хороший трюк, чтобы время от времени задирать рукава. Никогда не забывайте, что вы можете поместить почти произвольные выражения столбцов в предложение ORDER BY оператора SQL. Используй их!

Ссылка: Как реализовать косвенную сортировку в SQL от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ .
44
Share: