Oracle SQL - 具有 NULL 值的 max()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13417928/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL - max() with NULL values
提问by nibeck
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.
我有一个表，其中包含一系列基于时间的事件，每个事件都有一个开始和结束日期。对于最近（当前）事件，结束日期为 NULL。我试图折叠重复的行，只显示最早的开始日期和最晚的结束日期。由于日期字段中为 NULL，该行将被忽略。我可以用 NVL() 模拟一个结束日期值，但这会导致前端逻辑搜索并替换该值。
Is there anyway to get max() function to sort NULL as high?
有没有办法让 max() 函数将 NULL 排序为高？
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
)
This query works, but drops the row where end date is NULL.
此查询有效，但删除结束日期为 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;
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.
这个查询解决了这个问题，但现在我有一个“虚拟”结束日期值（9/9/9999）。我宁愿不必编码的东西。
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;
Thanks.
谢谢。
采纳答案by Kirill Leontev
max(end_dt) keep (dense_rank first order by end_dt desc nulls first)
max(end_dt) keep (dense_rank first order by end_dt desc nulls first)
upd:
更新：
Oracle 11g R2 Schema Setup:
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
;
Query 1:
查询 1：
select val, min(s), max(e) keep (dense_rank first order by e desc nulls first)
from t group by val
结果：
| 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 |
