sql - 使用 SQL 对具有但具有预先确定的覆盖值的列表进行排序

Question

业务问题有点晦涩，就不细说了。

我必须为一组键提出一个排序索引，但其中一些键在索引中有一个预先确定的位置，必须遵守这一点。其余键必须按正常顺序排列，但“围绕”预先确定的键。

简单的例子就是对字母A到E进行排序，只是A必须在位置3，D必须在位置1。我要实现的结果是:

A: 3 B: 2 C: 4 D: 1 E: 5

DDL 设置示例:

CREATE TABLE test.element (element_key TEXT, override_sort_idx INTEGER);

insert into test.element VALUES ('A', 3), ('B', Null), ('C', NULL), ('D', 1), ('E', NULL);

我能想到的最好的解决方案是这个，但尽管它似乎适用于这个简单的例子，但在一般情况下它会出错 - 如果你添加一些更多的预定义值它就会崩溃[编辑 - 它不会'甚至在这个例子中工作，因为 A 以 4 出现 - 抱歉]:

WITH inner_sort AS (SELECT element_key, override_sort_idx, row_number()
OVER (ORDER BY element_key) AS natural_sort_idx
              FROM test.element)
SELECT element_key, row_number()
    OVER
     (ORDER BY
      CASE
      WHEN override_sort_idx IS NULL
      THEN natural_sort_idx
      ELSE override_sort_idx END) AS hybrid_sort
FROM inner_sort;

对于适用于一般情况的解决方案有什么想法吗？

Answer 1

事实证明，这比我最初预期的更具挑战性。

但是这个 SQL 返回了预期的结果:

WITH OPENNUMBERS AS
(
  select row_number() over () as num
  from test.element 
  except
  select override_sort_idx
  from test.element 
  where override_sort_idx is not null
)
, OPENNUMBERS2 AS
(
  select num, row_number() over (order by num) as rn
  from OPENNUMBERS
)
,NORMALS AS
(
    select element_key, row_number() over (order by element_key) as rn
    from test.element
    where override_sort_idx is null
)
select n.element_key, o.num as hybrid_sort_idx
from OPENNUMBERS2 o
join NORMALS n ON n.rn = o.rn 
union all
select element_key, override_sort_idx
from test.element  
where override_sort_idx is not null
order by hybrid_sort_idx;

可以测试一下here在 SQL Fiddle 上。

用了什么招数？
获取删除覆盖后仍然可用的索引号列表。 (使用除外)
然后为这些数字和未覆盖的数字获取一个 row_number。加入 rownumber 上的行。
然后将其覆盖。