SQL where 子句使用 MAX Effective Date 和 NULL 值

SQL where clause uses MAX Effective Date and NULL values

提问by Joshua

I'm using a backend software to run SQL on Oracle. I need to fetch the latest row based on effective date.

我正在使用后端软件在 Oracle 上运行 SQL。我需要根据生效日期获取最新的行。

Here's an example table

这是一个示例表

Table "ACCOUNTS"
Application | Sub_category | Account_Reference | Effective_date
BC_ONLINE   | F            | ABC1234           | 01-JAN-13
BC_ONLINE   | B            | ABC2345           | 01-JAN-13
TE_Notice   | (NULL)       | 1234ABC           | 01-JAN-13
TE_Notice   | (NULL)       | 9876DEF           | 01-APR-13

The software will pass in two parameters: Application and Sub_category, if I used the following SQL, on the following Application: BC_ONLINEand Sub_category: F

软件会传入两个参数：Application和Sub_category，如果我用下面的SQL，在下面的Application：BC_ONLINE和Sub_category：F

select a.Account_Reference
from   ACCOUNTS a
where  a.Application = 'BC_ONLINE'
and    a.Sub_category = 'F'
and    a.Effective_date = (select max(b.Effective_date)
                           from   ACCOUNTS b
                           where  b.Effective_date <= sysdate
                           and    b.Application = a.Application
                           and    b.Sub_category = a.Sub_category)

BUT should I use the following Application: TE_Noticeand Sub_category: NULLand you cannot have this

但是我应该使用以下应用程序：TE_Notice和 Sub_category: NULL并且你不能有这个

and    a.Sub_category = null

since it must be

因为它必须是

and    a.Sub_category is null

the trouble is the software is fixed, so what is the best way to use both value and null in where clause using max(effective_date)?

问题是软件是固定的，那么在使用 max(effective_date) 的 where 子句中同时使用 value 和 null 的最佳方法是什么？

I have tried this method, but it doesn't work

我试过这个方法，但它不起作用

select a.Account_Reference
from   ACCOUNTS a
where  a.Application = 'TE_Notice'
and    (a.Sub_category = '' or a.Sub_category is null)
and    a.Effective_date = (select max(b.Effective_date)
                           from   ACCOUNTS b
                           where  b.Effective_date <= sysdate
                           and    b.Application = a.Application
                           and    NVL(b.Sub_category,-1) = NVL(a.Sub_category,-1))

It simply returned the row with 01-APR-13, but I should be getting the row with the Effective_date on 01-JAN-13.

它只是返回带有01-APR-13的行，但我应该在01-JAN-13上获取带有 Effective_date 的行。

采纳答案by Joshua

With thanks to @AlexPoole the SQL I put in the question is actually correct, but needed polishing.

感谢@AlexPoole，我在问题中提出的 SQL 实际上是正确的，但需要改进。

If I adopted the following:

如果我采用以下内容：

and (a.Sub_category = '' or a.Sub_category is null)

The select will fetch null rows as well as the non-null value, so need to improve;

select会获取空行以及非空值，所以需要改进；

and (a.Sub_category = '' or ('' is null and a.Sub_category is null))

The overall SQL should be

整体的SQL应该是

select a.Account_Reference
from   ACCOUNTS a
where  a.Application = @Application
and    (a.Sub_category = @Sub_category or (@Sub_category is null and a.Sub_category is null))
and    a.Effective_date = (select max(b.Effective_date)
                           from   ACCOUNTS b
                           where  b.Effective_date <= sysdate
                           and    b.Application = a.Application
                           and    NVL(b.Sub_category,-1) = NVL(a.Sub_category,-1))

回答by David Aldridge

How about:

怎么样：

 select account_reference
 from   (
        select   a.Account_Reference
        from     ACCOUNTS a
        where    a.Application = 'BC_ONLINE'
        and      a.Sub_category = 'F'
        and      a.effective_date <= sysdate
        order by a.Effective_date desc)
 where  rownum = 1