oracle 求连续日期的起始日期和终止日期

最新推荐文章于 2024-07-16 09:41:27 发布

最新推荐文章于 2024-07-16 09:41:27 发布 · 363 阅读

文章标签：

#数据库

本文介绍了一种利用Oracle SQL中的Connect By特性来查找日期序列中不连续的数据的方法。通过构造一个临时表并使用Start With和Connect By Prior子句，可以有效地找出缺失的日期区间。这种方法适用于需要检查日期序列完整性的场景。

//数据 1 2011-2-1 1 2011-2-2 1 2011-2-3 1 2011-2-11 1 2011-2-12 1 2011-2-13 //结果 1 2001-2-1 2011-2-3 1 2011-3-11 2011-2-13 //解法： with t as ( select 1 id, date'2011-2-1' mydate from dual union all select 1 id, date'2011-2-2' mydate from dual union all select 1 id, date'2011-2-3' mydate from dual union all select 1 id, date'2011-2-11' mydate from dual union all select 1 id, date'2011-2-12' mydate from dual union all select 1 id, date'2011-2-13' mydate from dual) SELECT id, MIN(mydate), MAX(mydate) FROM t START WITH NOT EXISTS (SELECT 1 FROM t b WHERE b.mydate = t.mydate - 1) CONNECT BY PRIOR t.mydate = t.mydate - 1 GROUP BY rownum - LEVEL, id;

原帖：

http://topic.youkuaiyun.com/u/20110602/09/02d22133-5427-4758-90fd-4f1519679994.html?95544

关于start with connect by:

[1]http://psoug.org/reference/connectby.html

[2]http://blog.youkuaiyun.com/BOBO12082119/archive/2011/03/19/6261344.aspx

connect by:

[1]http://www.dba-oracle.com/t_connect_by.htm