如何在SQL中实现排序间接

我最近偶然发现了一个有趣的Stack Overflow问题 ，该问题本质上是用户想要确保以明确定义的顺序交付结果记录。

他们写

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')

他们得到了

CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
GE526OTACCD3ANPH-149839
NI564FAACJSFANPH-162605

他们想

CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
NI564FAACJSFANPH-162605
GE526OTACCD3ANPH-149839

通常，根据您的业务规则，排序顺序不是数字排序或字母数字排序那样的“自然”排序。 一些业务规则可能指定了GE526OTACCD3ANPH-149839需要出现在列表的最后。 或者用户可能已经通过拖放在屏幕上重新排列了产品名称，从而产生了新的排序顺序。

当然，我们可以讨论是否应该在UI层中执行这种排序，但是让我们假设要在数据库中完成这种排序所需的业务案例或性能要求或通用体系结构。 怎么做？ 通过…

排序间接

实际上，您不想按产品名称排序，而是按此类名称的预定义枚举排序。 换句话说，您需要这样的功能：

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

使用普通的SQL，有许多方法可以完成上述操作。 这是其中两个（ 也可以在我的Stack Overflow答案中看到 ）：

通过使用CASE表达式

您可以使用ORDER BY子句中的CASE表达式轻松地告诉数据库显式排序间接CASE ：

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语法，但是您可以在数据库中以不同的方式实现相同的功能。

使用jOOQ的排序间接API

写间接排序有点麻烦 ，这就是为什么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);
      }}
   ))

结论

间接排序是一个时不时放松的好方法。 永远不要忘记，您可以在SQL语句的ORDER BY子句中放置几乎任意的列表达式。 使用它们！

翻译自: https://www.javacodegeeks.com/2014/05/how-to-implement-sort-indirection-in-sql.html