Oracle 重新排序alpha列中的选择性行

嗨，大师们，

下面的例子试图在B之前交换A，当它们一起出现时，同时忽略colC中的空值，并且仍然遵循colA的原始顺序，但是在id = 3333和4444中，对于一些例子B在a之前出现，并且空值位于两者之间也是。我花了几天时间试图合并lag和lead函数，以及尝试将变量传递到实际生产数据集的lag/lead位置20的函数传递到一个大小写语句中，但仍然有一些B前缀a的情况。

我已经尝试过搜索这个和一些其他来源，但努力寻找一个工作的解决方案，所以我想问。

以下结果中的缺点是:
3333，3,1，A应该出现在3333，2,2，B之前
3333，7,1，NULL错误地放置在3333，6,1，A和3333，5,2，B之间。它应该在3333，5,2，B之后
4444，2,1，A应该出现在4444，1,2，B之前，然后其余的应该跟随A，B第二。

with t1 as (select   1111 as id, 1 as cola, 2 as colb, 'B' as colc from dual
            union
            select   1111 as id, 2 as cola, 1 as colb, 'A' as colc from dual
            union
            select   1111 as id, 1 as cola, 2 as colb, 'B' as colc from dual
            union
            select   2222 as id, 2 as cola, 1 as colb, 'A' as colc from dual
            union
            select   2222 as id, 3 as cola, 2 as colb, 'B' as colc from dual
            union
            select   3333 as id, 1 as cola, 1 as colb, 'A' as colc from dual
            union
            select   3333 as id, 2 as cola, 1 as colb, '' as colc  from dual
            union
            select   3333 as id, 2 as cola, 2 as colb, 'B' as colc from dual
            union
            select   3333 as id, 3 as cola, 1 as colb, 'A' as colc from dual
            union
            select   3333 as id, 3 as cola, 2 as colb, 'B' as colc from dual
            union
            select   3333 as id, 4 as cola, 1 as colb, '' as colc  from dual
            union
            select   3333 as id, 5 as cola, 2 as colb, 'B' as colc from dual
            union
            select   3333 as id, 5 as cola, 2 as colb, 'B' as colc from dual
            union
            select   3333 as id, 6 as cola, 1 as colb, 'A' as colc from dual
            union
            select   3333 as id, 7 as cola, 1 as colb, '' as colc  from dual
            union
            select   4444 as id, 1 as cola, 1 as colb, '' as colc  from dual
            union
            select   4444 as id, 1 as cola, 2 as colb, 'B' as colc from dual
            union
            select   4444 as id, 2 as cola, 1 as colb, 'A' as colc from dual
            union
            select   4444 as id, 2 as cola, 2 as colb, 'B' as colc from dual
            union
            select   4444 as id, 3 as cola, 1 as colb, 'A' as colc from dual)
  select   id, cola,  colb, colc,
           dense_rank () over (partition by id order by mn, rn) as rn2
    from   (select   id, cola, colb, colc,
                     min (colb) over (partition by id, cola) as mn,
                     row_number () over (partition by id order by cola) rn
              from   t1)
order by   id, rn2

所以，当你说:

swap the A before B when they appear together

你到底是如何定义 “一起出现” 的？

如果您的意思是 “在无序选择的输出中，行彼此相邻”，那么您的要求是不可能的。

Oracle数据库对添加到堆表的行不施加任何顺序。This "order" can change。

例如:

create table t enable row movement as 
  select rownum x, mod(rownum, 3) y, lpad('x', 1000, 'x') str 
  from   dual
  connect by level <= 10;
  
select x from t;

X    
   1 
   2 
   3 
   4 
   5 
   6 
   7 
   8 
   9 
  10 

delete t where y = 0;

alter table t shrink space;

select x from t;

X    
   1 
   2 
   8 
   4 
   5 
  10 
   7

So you need a set columns to indicate rows are "next" to each other。 Once you have this just add them to the order by as needed。

But if no such column group exists, then sorry, you're out of luck。