按语句排序-按多列排序

Question

我有这样的桌子：

A       B       C
-----------------------
111     3
                777
333     1
555     2
                333
777     4
888     5

因此，我按“B命令”的顺序排列，我得到的结果如下：

A       B       C
----------------------
333     1
555     2
111     3
777     4
888     5
                777
                333

然而，我能做些什么才能得到这个排序：

A       B       C
-----------------------
333     1
                333
555     2
111     3
777     4
                777
888     5

如果C列为null，我应该将这一行放在A=C的一行

谢谢!

因此，如果发生这种情况：

with a(a,b,c) as (select 111,4, null from dual union all
                  select null,null,777 from dual union all
                  select 333,1,null  from dual union all
                  select 555,2, null from dual union all
                  select null,null, 333 from dual union all
                  select 777, 4, null from dual union all

                  select 444,null, 333 from dual union all

                  select 888, 5, null from dual union all
                  select null,null,777 from dual )

select a.*
  from a
 order by last_value(b ignore nulls) 
       over (partition by CASE when b is null then c else a end order by b), b nulls last

我有输出(C 777在A 111之后，因为B值相同= 4)：

A    B     C
--------------------           
333     1   
444         333
            333
555     2   
777     4   
111     4   
            777
            777
888     5   

但我想得到这个：

    A    B     C
    --------------------
    333     1   
    444         333
                333
    555     2   
    777     4   
                777
                777
    111     4   
    888     5   

Answer 1

也许这对你有帮助：

with a(a,b,c) as (select 111,3, null from dual union all
                  select null,null,777 from dual union all
                  select 333,1,null  from dual union all
                  select 555,2, null from dual union all
                  select null,null, 333 from dual union all
                  select 777, 4, null from dual union all
                  select 888, 5, null from dual )

select a.* 
  from a
 order by last_value(b ignore nulls) over (partition by nvl(a,c) order by b), b nulls last

输出

333 1   
        333
555 2   
111 3   
777 4   
        777
888 5   

选定7行

或者，如您稍后所述，您可以同时拥有null和C列，您可以这样做：

with a(a,b,c) as (select 111,3, null from dual union all
                  select null,null,777 from dual union all
                  select 333,1,null  from dual union all
                  select 555,2, null from dual union all
                  select null,null, 333 from dual union all
                  select 777, 4, null from dual union all

                  select 444,null, 333 from dual union all

                  select 888, 5, null from dual )

select a.*
  from a
 order by last_value(b ignore nulls) 
       over (partition by CASE when b is null then c else a end order by b), b nulls last

输出

         A          B          C
       333          1            
                             333 
       444                   333 
       555          2            
       111          3            
       777          4            
                             777 
       888          5            

 8 rows selected 

Answer 2

这样做吧：

select case when C in not null then C else A end as A,B,C from table

就像这样：

Declare @c nchar(50)
Declare @a nchar(50)
set @c = 'record' 
set @a = 'sample'
select case  when  @c is not null then @c else @a end as A,@c as C