Oracle 根据自定义排序通过显示位置进行分组

我有一张表
id名称位置

 1 aaa home 
1 aaa village
 1 aaa office 
2. bbb village 
2. bbb office 
3. ccc home 
3. ccc office

当我写一个查询时，我应该得到每个有precendence home>office>village的单个记录

输出:

 1.aaa home
 2.bbb office
 3.ccc home

啊哈，max的dense_rank keep子句的完美用例!也令人困惑地 (对我来说) 被称为第一个函数。

首先你需要定义一个自定义排序。这将位置名称按照您想要的顺序映射到递增值。例如home -> 1，office -> 2，village -> 3，就像这样:

case 
  when loc = 'home' then 1
  when loc = 'office' then 2
  when loc = 'village' then 3
end

然后按id，名称对您的行进行分组。取最大位置，将此自定义排序传递给keep (dense_rank first ... ) 子句。这告诉数据库根据顺序对组中的行进行排序。它将此排序中首先出现的行的值传递给max。

所以对于你的数据，你会想要这样的东西:

max ( loc ) keep (
  dense_rank first 
  order by 
  case 
    when loc = 'home' then 1
    when loc = 'office' then 2
    when loc = 'village' then 3
  end
) loc

总的来说，这给出了:

create table t (
  id int, nm varchar2(3), loc varchar2(10)
);
insert into t values (1, 'aaa', 'home');
insert into t values (1, 'aaa', 'village');
insert into t values (1, 'aaa', 'office');
insert into t values (2, 'bbb', 'village');
insert into t values (2, 'bbb', 'office');
insert into t values (3, 'ccc', 'home');
insert into t values (3, 'ccc', 'office');
commit;

select id, nm,
       max ( loc ) keep (
         dense_rank first 
         order by 
         case 
           when loc = 'home' then 1
           when loc = 'office' then 2
           when loc = 'village' then 3
         end
       ) loc
from   t
group  by id, nm;

ID   NM    LOC      
   1 aaa   home     
   2 bbb   office   
   3 ccc   home