Oracle与Null一起加入

Question

我正在尝试获取每个提交号码的最新修订版，我通过使用LEFT JOIN找到了一种方法 - 我丢失了指向SO问题的链接。但是现在我的问题是，当提交号为 NULL时，它似乎比我期望的要多。

修订表

---------------------------------------------------
id     submission_number     revision_created
---------------------------------------------------
1000   B00018                11/24/2016 10:45:34 AM
1001   B00018                11/25/2016 09:01:22 AM
1002   B00018                11/25/2016 03:35:01 PM
1003   NULL                  11/26/2016 09:09:11 AM
1004   NULL                  11/30/2016 03:45:22 PM
2000   B00032                10/01/2016 02:00:45 PM
3000   B00041                11/03/2016 11:03:10 AM
3001   B00041                11/05/2016 09:51:09 AM

在这些示例中，我想获得

所需的输出

---------------------------------------------------
id     submission_number     revision_created
---------------------------------------------------
1002   B00018                11/25/2016 03:35:01 PM
1004   NULL                  11/30/2016 03:45:22 PM
2000   B00032                10/01/2016 02:00:45 PM
3001   B00041                11/05/2016 09:51:09 AM

基本上，我只想为每个提交编号获得1个修订版，这应该是最新的修订。这是我在stackoverflow中发现的查询：

查询

SELECT r.id, r.revision_created, r.submission_number
FROM revision r
  LEFT JOIN revision jt
    ON (
      r.submission_number = jt.submission_number
      AND r.revision_created < jt.revision_created
    )
WHERE jt.revision_created IS NULL

如果提交编号具有值，则上述查询有效。如果是NULL，我只会期望1时会得到很多行。

我的尝试解决方案

我试图将JOIN ON子句更改为

ON (
  r.submission_number = jt.submission_number OR (r.submission_number IS NULL AND jt.submission_number IS NULL)
  AND r.revision_created < jt.revision_created
)

但我的更改实际上删除了提交号为 NULL的修订版。

限制

在我忘记之前，我应该在一个软件中进行此操作，在该软件中，我可以使用我可以使用的甲骨文功能。因此，不支持MAX，MIN，REPLACE等。

Answer 1

假设MIN和MAX的分析版本（而不是汇总版本）超出界限，您可以做这样的事情：

准备（所以我不需要弄乱to_date()和to_char()）：

SQL> alter session set nls_date_format = 'mm/dd/yyyy hh:mi:ss AM';
Session altered.

然后：

with
     revision ( id, submission_number, revision_created ) as (
       select 1000, 'B00018', '11/24/2016 10:45:34 AM' from dual union all
       select 1001, 'B00018', '11/25/2016 09:01:22 AM' from dual union all
       select 1002, 'B00018', '11/25/2016 03:35:01 PM' from dual union all
       select 1003, NULL    , '11/26/2016 09:09:11 AM' from dual union all
       select 1004, NULL    , '11/30/2016 03:45:22 PM' from dual union all
       select 2000, 'B00032', '10/01/2016 02:00:45 PM' from dual union all
       select 3000, 'B00041', '11/03/2016 11:03:10 AM' from dual union all
       select 3001, 'B00041', '11/05/2016 09:51:09 AM' from dual
     )
-- end of test data; solution (SQL query) begins below this line
select r.id, r.submission_number, r.revision_created
from   revision r inner join
       ( select submission_number, max(revision_created) as max_created
         from   revision
         group by submission_number
       ) g
       on  ( r.submission_number = g.submission_number
                  or r.submission_number is null and g.submission_number is null
           )
           and r.revision_created  = g.max_created
;
   ID SUBMISSION_NUMBER REVISION_CREATED
----- ----------------- ----------------------
 1001 B00018            11/25/2016 09:01:22 AM
 1004                   11/30/2016 03:45:22 PM
 2000 B00032            10/01/2016 02:00:45 PM
 3001 B00041            11/05/2016 09:51:09 AM
4 rows selected.

Answer 2

您可以根据clobs in Min Revision_created

使用子查询

  SELECT r.id, r.revision_created, r.submission_number
  from revision r 
  where  (r.submission_number, r.revision_created) in ( 
      select submission_number, min(revision_created) 
      from revision
      group by submission_number
  ) 

Answer 3

您只需将NULL加入条件添加到您的原始尝试中，例如：

SELECT r.id, r.revision_created, r.submission_number
FROM revision r
  LEFT JOIN revision jt
    ON (
      ( r.submission_number = jt.submission_number 
        OR ( r.submission_Number is null and jt.submission_number is null ) )
      AND r.revision_created < jt.revision_created
    )
WHERE jt.revision_created IS NULL;

这绝对不是在Oracle中做到这一点的最佳方法，但我认为它遵循您给出的局限性。

Answer 4

这是一个仅需要表的扫描的版本...

with
     revision ( id, submission_number, revision_created ) as (
       select 1000, 'B00018', '11/24/2016 10:45:34 AM' from dual union all
       select 1001, 'B00018', '11/25/2016 09:01:22 AM' from dual union all
       select 1002, 'B00018', '11/25/2016 03:35:01 PM' from dual union all
       select 1003, NULL    , '11/26/2016 09:09:11 AM' from dual union all
       select 1004, NULL    , '11/30/2016 03:45:22 PM' from dual union all
       select 2000, 'B00032', '10/01/2016 02:00:45 PM' from dual union all
       select 3000, 'B00041', '11/03/2016 11:03:10 AM' from dual union all
       select 3001, 'B00041', '11/05/2016 09:51:09 AM' from dual
     )
select id, submission_number, revision_created 
from (
       select id, submission_number, revision_created 
        ,last_value( revision_created ) 
            over ( partition by submission_number order by revision_created
            rows  between unbounded preceding and unbounded following ) lv
       from revision
      )
where revision_created = lv