Oracle SQL-带有NULL值的max() [英] Oracle SQL - max() with NULL values

问题描述

我有一个表，该表包含一系列基于时间的事件，每个事件都有开始和结束日期.对于最新(当前)事件，结束日期为NULL.我试图折叠重复的行，只显示最早的开始日期和最新的结束日期.在日期字段中为NULL的情况下，该行将被忽略.我可以使用NVL()虚设结束日期值，但这将导致前端逻辑搜索并替换该值.

I have a table that has a series of time based events, each bound with a start and end date. For the most recent (current) event, the end date is NULL. Im trying to collapse the duplicative rows and only show the earliest start date and the latest end date. With the NULL being in the date field, that row is ignored. I can dummy up an end date value with NVL(), but that will cause the front end logic to search for and replace that value.

反正有没有要获得max()函数来对NULL进行高排序的问题?

Is there anyway to get max() function to sort NULL as high?

CREATE TABLE CONG_MEMBER_TERM
(
  CONG_MEMBER_TERM_ID  NUMBER(10)               NOT NULL,
  CHAMBER_CD           VARCHAR2(30 BYTE)        NOT NULL,
  CONG_MEMBER_ID       NUMBER(10)               NOT NULL,
  STATE_CD             CHAR(2 BYTE)             NOT NULL,
  DISTRICT             NUMBER(10),
  START_DT             TIMESTAMP(6) WITH TIME ZONE,
  END_DT               TIMESTAMP(6) WITH TIME ZONE
)

此查询有效，但是删除结束日期为NULL的行.

This query works, but drops the row where end date is NULL.

select CONG_MEMBER_ID, 
       district, 
       min(start_dt), 
       max(end_dt)
  from CONG_MEMBER_TERM
 where CONG_MEMBER_ID = 1716
 group by CONG_MEMBER_ID, district;

此查询可解决此问题，但现在我有一个虚拟"结束日期值(9/9/9999).我宁愿不必编写代码.

This query fixes that, but now I have a "dummy" end date value(9/9/9999). Something I would rather not have to code around.

select CONG_MEMBER_ID, 
       district, 
       min(start_dt), 
       max(nvl(end_dt, to_date('9/9/9999', 'mm/dd/yyyy')))
  from CONG_MEMBER_TERM
 where CONG_MEMBER_ID = 1716
 group by CONG_MEMBER_ID, district;

谢谢.

推荐答案

max(end_dt) keep (dense_rank first order by end_dt desc nulls first)

更新:

SQL小提琴

Oracle 11g R2架构设置:

CREATE TABLE t
    (val int, s date, e date)
;

INSERT ALL 
    INTO t (val, s, e)
         VALUES (1, sysdate-3, sysdate-2)
    INTO t (val, s, e)
         VALUES (1, sysdate-2, sysdate-1)
    INTO t (val, s, e)
         VALUES (1, sysdate-1, null)
    INTO t (val, s, e)
         VALUES (2, sysdate-1, sysdate-.5)
    INTO t (val, s, e)
         VALUES (2, sysdate-.5, sysdate-.25)
SELECT * FROM dual
;

查询1 :

select val, min(s), max(e) keep (dense_rank first order by e desc nulls first)
from t group by val

结果 :

Results:

| VAL |                          MIN(S) | MAX(E)KEEP(DENSE_RANKFIRSTORDERBYEDESCNULLSFIRST) |
---------------------------------------------------------------------------------------------
|   1 | November, 13 2012 14:15:46+0000 |                                            (null) |
|   2 | November, 15 2012 14:15:46+0000 |                   November, 16 2012 08:15:46+0000 |

这篇关于Oracle SQL-带有NULL值的max()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！