SQL中如何返回最大值或空值？

发布于 2024-09-13 08:23:13 字数 673 浏览 17 评论 0原文

我正在 Oracle 数据库上运行请求。我的一个表包含元素的时间段，并且同一元素可以有多个时间段，因此以下示例是正确的：

ID    ELEMENT_ID    BEGIN_DATE    END_DATE
--------------------------------------------
1     1             01/01/2007    01/06/2007
2     1             01/06/2007   

3     2             01/01/2006    01/07/2006
4     2             01/07/2006    31/12/2006

END_DATE 未填充的时间段意味着它仍在运行（因此，这是元素的最新时间段）。

因此，当我搜索每个元素的最新 END_DATE 时，我想获取第 #2 行和第 #4 行。所以，我面临的问题是将 NULL 视为最高的 END_DATE ...

是否可以在一个 SQL 请求中完成此操作？

当然，我尝试了一个简单的：

SELECT MAX(END_DATE) FROM ...

但由于 NULL 值，这还不够。

提前致谢。

原文

I'm running requests on an Oracle database.
One of my tables contains time slots for Elements and there can be several time slots for the same Element so that the following example is correct :

ID    ELEMENT_ID    BEGIN_DATE    END_DATE
--------------------------------------------
1     1             01/01/2007    01/06/2007
2     1             01/06/2007   

3     2             01/01/2006    01/07/2006
4     2             01/07/2006    31/12/2006

The time slot for which END_DATE is not filled means that it is still running (so, this is the most recent time slot for the Element).

Thus, when I search the most recent END_DATE for each Element, I want to obtain the rows #2 and #4.
So, The problem I'm facing is to consider NULL as the highest END_DATE ...

Is it possible to do this in one single SQL request ?

Of course I tried a simple :

SELECT MAX(END_DATE) FROM ...

but it's not enought because of the NULL values.

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迟到的我 2024-09-20 08:23:13

将空值更改为某个未来值

SELECT MAX(NVL(END_DATE,SYSDATE + 1) ...

Change the null values to some future value

SELECT MAX(NVL(END_DATE,SYSDATE + 1) ...

回复收藏 0 原文

半边脸i 2024-09-20 08:23:13

尝试：

select element_id, max(coalesce(end_date, date '4000-12-31')) as max_end_date
from ...

Try:

select element_id, max(coalesce(end_date, date '4000-12-31')) as max_end_date
from ...

回复收藏 0 原文

姜生凉生 2024-09-20 08:23:13

使用 ORDER BY 和 NULLS FIRST，例如：

SELECT DISTINCT
       FIRST_VALUE(id)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id,
       element_id,
       FIRST_VALUE(begin_date)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id,
       FIRST_VALUE(end_date)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id
FROM   ...


ID    ELEMENT_ID    BEGIN_DATE    END_DATE
--------------------------------------------
2     1             01/06/2007   
4     2             01/07/2006    31/12/2006

Use an ORDER BY with NULLS FIRST, e.g.:

SELECT DISTINCT
       FIRST_VALUE(id)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id,
       element_id,
       FIRST_VALUE(begin_date)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id,
       FIRST_VALUE(end_date)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id
FROM   ...


ID    ELEMENT_ID    BEGIN_DATE    END_DATE
--------------------------------------------
2     1             01/06/2007   
4     2             01/07/2006    31/12/2006

回复收藏 0 原文

~没有更多了~