加入甲骨文的最佳记录（如果有的话）

Question

我有一个相当复杂的Oracle查询，从多个表中获取数据。在其中一个联接中，我想要最好的记录，如果有的话。因此，一个左向外连接。有一个开始日期字段，所以对于大多数记录来说，获得最大开始日期将使我获得最好的记录。但是，偶尔会有具有相同开始日期的记录。在这种情况下，还有一个状态字段。然而，最好的状态值不是min或max。“20”是最好的，“05”或“40”是可以的，“70”是最差的。当返回多个记录时，我如何设置查询以找到最佳选项？

如果我有以下数据

Table1                       Table2
ID     otherData             ID      date      status    otherData
1      stuffa                1       jan-1-13  20        stuff93
2      stuff3                
3      stuff398              3       jan-2-13  20        stuff92
                             3       jan-2-13  70        stuff38
                             3       dec-3-12  20        stuff843

我将能够查询并获得以下内容：

1   stuffa    jan-1-13  20  stuff93
2   stuff3
3   stuff398  jan-2-13  20  stuff92

现在，我的查询如下，它得到第二条记录3，状态为70：

select *
from table1 t1
left outer join
   (select *
    from table2 t2a
    where t2a.date = (select max(t2b.date)
                        from table2 t2b
                       where t2b.id = t2a.id)
    ) t2
  on (t2.id = t1.id)

有没有办法在select语句中设置有序枚举之类的内容？差不多

rank() over ( partition by status order by ('20','05','40','70') rank

Answer 1

你可以这样做：

select t1.id, t1.otherdata, t2.dt, t2.status, t2.otherdata 
from table1 t1
left outer join (
    select t2a.*,
        row_number() over (partition by id order by dt desc,
            case status
                when '20' then 1
                when '05' then 2
                when '40' then 3
                when '70' then 4
                else 5 end) as rn
    from table2 t2a
    ) t2 on t2.id = t1.id and t2.rn = 1
order by t1.id;

这假设你想要一次点击，即使有两个状态相同；这两种情况中哪一种是不确定的。如果两者都需要，可以使用rank（）。无论哪种方式，您都是根据日期（降序，因为您想要最大值）和您自己的状态值顺序为每个记录分配一个等级，然后只选择连接条件中排名最高的记录。

数据设置为：

create table table1(id number, otherdata varchar2(10));
create table table2(id number, dt date, status varchar2(2), otherdata varchar2(10));

insert into table1 values(1, 'stuffa');
insert into table1 values(2, 'stuff3');
insert into table1 values(3, 'stuff398');

insert into table2 values(1, date '2013-01-01', '20', 'stuff93');
insert into table2 values(3, date '2013-01-02', '20', 'stuff92');
insert into table2 values(3, date '2013-01-02', '70', 'stuff38');
insert into table2 values(3, date '2012-12-03', '20', 'stuff843');

... 这使得：

        ID OTHERDATA  DT        STATUS OTHERDATA
---------- ---------- --------- ------ ----------
         1 stuffa     01-JAN-13 20     stuff93    
         2 stuff3                                 
         3 stuff398   02-JAN-13 20     stuff92

Answer 2

如果有序枚举的元素很少，您可以使用这个

 ........         order by 
                  CASE status WHEN '20' THEN 1
                              WHEN '05' THEN 2
                              WHEN '40' THEN 3
                              WHEN '70' THEN 4
                  END) rank

Answer 3

通过以下方式将状态添加到订单中：；

select *
  from (select t1.id,  t1.otherdata otherdatat1, t2.date, t2.status, t2.otherdata otherdatat2,
               rank() over (partition by t1.id order by t2.date desc,
                            case t2.status
                              when '20' then 1
                              when '05' then 2
                              when '40' then 3
                              when '70' then 4
                              else 5
                            end) rnk
          from table1 t1
               left outer join table2 t2
                            on t1.id = t2.id)
 where rnk = 1;