http://topic.csdn.net/u/20090923/13/baaa34b4-184d-4805-b6d4-747ec0845fe6.html
曾经我发过一个这样的帖子
还是这样的一个问题 还是时间的连续判断
只不过是和上一个点不一样
现在的条件是这样的
在连续登记的每年内,登记45天中间断不超过1天。只间断1天应考虑为管理性质,而不算作性质不同的等级空缺。
现在把问题简单化 就是在2005-01-01 至2006-01-01 之间判断连续。
这个连续的条件是什么呢!就是登记45天中 间断不超过1天,说白了就是登记45天之间只能有一天的断档。
举例说明:就是说这个人从2005-01-01 登记 到2005-02-15 是登记45天,但是在这45天之内只允许有一天的间断。比如说他登记的时间是这样的 从2005-01-01 开始登记 到2005-01-15 结束 然后又从 2005-01-16开始 到2005-01-25 结束 然后又从 2005-01-27 开始到 2005-02-15 结束 这个人登记的45天之内(从2005-01-01 到 2005-02-15)就只有一天是间断的。
就是一个这样的过程,现在需要得到的就是都有哪些人符合这个条件。
我现在在和大家说一下这个要查询的表结构。
有这样的一个表hist 表里有三个字段 一个是mbrid (就是人的id) 一个是effdat (登记开始的时间) 一个是expdat(登记结束时间) 一个人可能有多次登记 ,而且登记的时间可能是包含的关系。也可能是不连续的下面是表中的一些数据。 MBRID EFFDAT EXPDAT
100000 2005-1-1 2005-3-31
100000 2005-1-1 2005-12-31
100000 2005-4-1 2005-12-31
100000 2005-7-1 2005-12-31
100000 2006-1-1 2006-3-31
100000 2006-1-1 2006-12-31
100000 1997-1-1 1997-3-16
100000 1997-3-16 1997-12-31
100000 1998-1-1 1998-12-31
100000 1999-1-1 1999-12-31
100000 1999-7-1 2000-1-1
100000 2000-1-1 2000-12-31
100000 2000-4-1 2001-1-1
100000 2000-7-1 2001-1-1
100000 2001-1-1 2001-6-30
100000 2001-4-1 2002-1-1
100000 2002-1-1 2002-5-18
100000 2002-4-1 2002-5-18
100000 2002-5-19 2002-12-31
100000 2003-1-1 2003-3-31
100000 2003-1-1 2003-12-31
100000 2003-4-1 2003-12-31
100000 2004-1-1 2004-3-31
100000 2004-1-1 2004-12-31
100000 2004-4-1 2004-12-31
1000001 2003-7-1 2003-12-31
1000001 2003-7-1 2004-3-31
1000001 2004-1-1 2004-6-30
1000001 2004-4-1 2004-6-30
1000001 2004-7-1 2004-8-15
1000001 2005-6-1 2005-6-30
1000001 2005-7-1 2005-12-31
1000001 2005-7-1 2006-3-31
1000001 2005-7-1 2006-6-30
1000001 2006-7-1 2006-12-31
1000001 1997-3-1 1997-7-1
1000001 1998-1-1 1998-7-1
1000001 1998-7-1 1998-12-31
1000001 1999-1-1 1999-7-1
1000001 2002-1-1 2002-6-30
1000001 2002-4-1 2002-6-30
1000001 2002-7-1 2002-12-31
1000001 2002-7-1 2003-3-31
1000001 2003-1-1 2003-6-30
1000001 2003-4-1 2003-6-30
1000002 1997-1-1 1997-7-1
1000002 1997-7-1 1997-12-31
1000002 1998-1-1 1998-7-1
1000002 1998-7-1 1998-12-1
1000002 1999-1-1 1999-7-1
1000002 1999-7-1 2000-6-30
1000002 2000-1-1 2000-7-1
1000002 2000-4-1 2000-7-1
1000002 2000-7-1 2001-6-30
1000002 2001-4-1 2001-7-1
1000002 2001-7-1 2002-2-1
1000002 2002-1-1 2002-1-31
1000004 1997-7-1 1997-12-31
1000004 1998-1-1 1998-7-1
1000004 1998-12-1 1998-12-31
1000004 1999-7-1 2000-6-30
1000004 2000-7-1 2001-6-30
1000004 2001-4-1 2001-7-1
1000004 2001-7-1 2002-3-31
1000004 2002-1-1 2002-6-30
1000004 2002-4-1 2002-6-30
1000004 2002-7-1 2002-12-31
1000004 2002-7-1 2003-3-31
1000004 2003-1-1 2003-6-30
1000004 2003-4-1 2003-6-30
1000004 2003-7-1 2003-12-31
1000004 2003-7-1 2004-3-31
1000004 2004-1-1 2004-6-30
1000004 2004-4-1 2004-6-30
1000004 2004-7-1 2004-12-31
1000004 2004-7-1 2005-1-31
1000004 2005-1-1 2005-1-31
曾经我发过一个这样的帖子
还是这样的一个问题 还是时间的连续判断
只不过是和上一个点不一样
现在的条件是这样的
在连续登记的每年内,登记45天中间断不超过1天。只间断1天应考虑为管理性质,而不算作性质不同的等级空缺。
现在把问题简单化 就是在2005-01-01 至2006-01-01 之间判断连续。
这个连续的条件是什么呢!就是登记45天中 间断不超过1天,说白了就是登记45天之间只能有一天的断档。
举例说明:就是说这个人从2005-01-01 登记 到2005-02-15 是登记45天,但是在这45天之内只允许有一天的间断。比如说他登记的时间是这样的 从2005-01-01 开始登记 到2005-01-15 结束 然后又从 2005-01-16开始 到2005-01-25 结束 然后又从 2005-01-27 开始到 2005-02-15 结束 这个人登记的45天之内(从2005-01-01 到 2005-02-15)就只有一天是间断的。
就是一个这样的过程,现在需要得到的就是都有哪些人符合这个条件。
我现在在和大家说一下这个要查询的表结构。
有这样的一个表hist 表里有三个字段 一个是mbrid (就是人的id) 一个是effdat (登记开始的时间) 一个是expdat(登记结束时间) 一个人可能有多次登记 ,而且登记的时间可能是包含的关系。也可能是不连续的下面是表中的一些数据。 MBRID EFFDAT EXPDAT
100000 2005-1-1 2005-3-31
100000 2005-1-1 2005-12-31
100000 2005-4-1 2005-12-31
100000 2005-7-1 2005-12-31
100000 2006-1-1 2006-3-31
100000 2006-1-1 2006-12-31
100000 1997-1-1 1997-3-16
100000 1997-3-16 1997-12-31
100000 1998-1-1 1998-12-31
100000 1999-1-1 1999-12-31
100000 1999-7-1 2000-1-1
100000 2000-1-1 2000-12-31
100000 2000-4-1 2001-1-1
100000 2000-7-1 2001-1-1
100000 2001-1-1 2001-6-30
100000 2001-4-1 2002-1-1
100000 2002-1-1 2002-5-18
100000 2002-4-1 2002-5-18
100000 2002-5-19 2002-12-31
100000 2003-1-1 2003-3-31
100000 2003-1-1 2003-12-31
100000 2003-4-1 2003-12-31
100000 2004-1-1 2004-3-31
100000 2004-1-1 2004-12-31
100000 2004-4-1 2004-12-31
1000001 2003-7-1 2003-12-31
1000001 2003-7-1 2004-3-31
1000001 2004-1-1 2004-6-30
1000001 2004-4-1 2004-6-30
1000001 2004-7-1 2004-8-15
1000001 2005-6-1 2005-6-30
1000001 2005-7-1 2005-12-31
1000001 2005-7-1 2006-3-31
1000001 2005-7-1 2006-6-30
1000001 2006-7-1 2006-12-31
1000001 1997-3-1 1997-7-1
1000001 1998-1-1 1998-7-1
1000001 1998-7-1 1998-12-31
1000001 1999-1-1 1999-7-1
1000001 2002-1-1 2002-6-30
1000001 2002-4-1 2002-6-30
1000001 2002-7-1 2002-12-31
1000001 2002-7-1 2003-3-31
1000001 2003-1-1 2003-6-30
1000001 2003-4-1 2003-6-30
1000002 1997-1-1 1997-7-1
1000002 1997-7-1 1997-12-31
1000002 1998-1-1 1998-7-1
1000002 1998-7-1 1998-12-1
1000002 1999-1-1 1999-7-1
1000002 1999-7-1 2000-6-30
1000002 2000-1-1 2000-7-1
1000002 2000-4-1 2000-7-1
1000002 2000-7-1 2001-6-30
1000002 2001-4-1 2001-7-1
1000002 2001-7-1 2002-2-1
1000002 2002-1-1 2002-1-31
1000004 1997-7-1 1997-12-31
1000004 1998-1-1 1998-7-1
1000004 1998-12-1 1998-12-31
1000004 1999-7-1 2000-6-30
1000004 2000-7-1 2001-6-30
1000004 2001-4-1 2001-7-1
1000004 2001-7-1 2002-3-31
1000004 2002-1-1 2002-6-30
1000004 2002-4-1 2002-6-30
1000004 2002-7-1 2002-12-31
1000004 2002-7-1 2003-3-31
1000004 2003-1-1 2003-6-30
1000004 2003-4-1 2003-6-30
1000004 2003-7-1 2003-12-31
1000004 2003-7-1 2004-3-31
1000004 2004-1-1 2004-6-30
1000004 2004-4-1 2004-6-30
1000004 2004-7-1 2004-12-31
1000004 2004-7-1 2005-1-31
1000004 2005-1-1 2005-1-31
像上面的示例中有这么条记录
100000 2001-1-1 2001-6-30
明显超过45天。可是在这一年里,如果有另一次登记时间和这一次的间隔超过一天,那么100000在2001年是连续还是不连续?
要查出的是连续的id和年份还是不连续的?楼主能否贴上测试数据的sql脚本语句(可用pl/sql dev导出)..一条一条插入太麻烦了
values ('1000009', to_date('01-01-1997', 'dd-mm-yyyy'), to_date('01-07-1997', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-07-1997', 'dd-mm-yyyy'), to_date('01-09-1997', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-09-1997', 'dd-mm-yyyy'), to_date('31-12-1997', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-01-1998', 'dd-mm-yyyy'), to_date('01-06-1998', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-06-1998', 'dd-mm-yyyy'), to_date('01-07-1998', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-07-1998', 'dd-mm-yyyy'), to_date('01-12-1998', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-12-1998', 'dd-mm-yyyy'), to_date('31-12-1998', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-01-1999', 'dd-mm-yyyy'), to_date('01-07-1999', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-07-2000', 'dd-mm-yyyy'), to_date('30-06-2001', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-04-2001', 'dd-mm-yyyy'), to_date('01-07-2001', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-01-2002', 'dd-mm-yyyy'), to_date('30-06-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-04-2002', 'dd-mm-yyyy'), to_date('30-06-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000009', to_date('01-07-2002', 'dd-mm-yyyy'), to_date('31-07-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-04-2001', 'dd-mm-yyyy'), to_date('01-01-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2002', 'dd-mm-yyyy'), to_date('31-12-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-04-2002', 'dd-mm-yyyy'), to_date('31-12-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2003', 'dd-mm-yyyy'), to_date('31-03-2003', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2003', 'dd-mm-yyyy'), to_date('31-12-2003', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-04-2003', 'dd-mm-yyyy'), to_date('31-12-2003', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2004', 'dd-mm-yyyy'), to_date('31-03-2004', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2004', 'dd-mm-yyyy'), to_date('31-12-2004', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-04-2004', 'dd-mm-yyyy'), to_date('31-12-2004', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2005', 'dd-mm-yyyy'), to_date('31-03-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2005', 'dd-mm-yyyy'), to_date('30-09-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-04-2005', 'dd-mm-yyyy'), to_date('30-09-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-07-2005', 'dd-mm-yyyy'), to_date('30-09-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-10-2005', 'dd-mm-yyyy'), to_date('31-12-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2006', 'dd-mm-yyyy'), to_date('31-03-2006', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-01-2006', 'dd-mm-yyyy'), to_date('14-04-2006', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('15-04-2006', 'dd-mm-yyyy'), to_date('30-04-2006', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('01-05-2006', 'dd-mm-yyyy'), to_date('26-09-2006', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('100001', to_date('27-09-2006', 'dd-mm-yyyy'), to_date('31-12-2006', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-01-1997', 'dd-mm-yyyy'), to_date('01-07-1997', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-01-1999', 'dd-mm-yyyy'), to_date('01-10-1999', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-1999', 'dd-mm-yyyy'), to_date('01-10-1999', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-10-1999', 'dd-mm-yyyy'), to_date('30-06-2000', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-04-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-10-2000', 'dd-mm-yyyy'), to_date('30-06-2001', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-04-2001', 'dd-mm-yyyy'), to_date('01-10-2001', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-10-2001', 'dd-mm-yyyy'), to_date('01-01-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-01-2002', 'dd-mm-yyyy'), to_date('28-02-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-10-2002', 'dd-mm-yyyy'), to_date('31-12-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-10-2002', 'dd-mm-yyyy'), to_date('31-03-2003', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-01-2003', 'dd-mm-yyyy'), to_date('30-06-2003', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-04-2003', 'dd-mm-yyyy'), to_date('30-06-2003', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-2003', 'dd-mm-yyyy'), to_date('31-12-2003', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-2003', 'dd-mm-yyyy'), to_date('31-03-2004', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-01-2004', 'dd-mm-yyyy'), to_date('30-06-2004', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-04-2004', 'dd-mm-yyyy'), to_date('30-06-2004', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-2004', 'dd-mm-yyyy'), to_date('31-12-2004', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-2004', 'dd-mm-yyyy'), to_date('31-03-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-01-2005', 'dd-mm-yyyy'), to_date('30-06-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-04-2005', 'dd-mm-yyyy'), to_date('30-06-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-2005', 'dd-mm-yyyy'), to_date('30-11-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-02-2002', 'dd-mm-yyyy'), to_date('14-04-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-04-2002', 'dd-mm-yyyy'), to_date('14-04-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('15-04-2002', 'dd-mm-yyyy'), to_date('31-08-2002', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-11-2005', 'dd-mm-yyyy'), to_date('31-12-2005', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-11-2005', 'dd-mm-yyyy'), to_date('31-03-2006', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-11-2005', 'dd-mm-yyyy'), to_date('30-06-2006', 'dd-mm-yyyy'));
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
values ('1000011', to_date('01-07-2006', 'dd-mm-yyyy'), to_date('31-12-2006', 'dd-mm-yyyy'));
脚本的第一句缺一行 不好意思
insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
对了 我这还有你上个帖子 也就是那个连续判断的那个的代码select mbrid from(
select mbrid,sum(case when effdat>lg+1 then 1 else 0 end)s,min(effdat)mi,max(expdat)ma
from(
select mbrid,effdat,expdat,
lag(expdat)over(partition by mbrid order by expdat)lg from (select * from mbr_hist where effdat <= to_date('2006-01-01','yyyy-mm-dd') and expdat >= to_date('2005-01-01','yyyy-mm-dd')))
where effdat <=to_date('2006-01-01','yyyy-mm-dd') and expdat>=to_date('2005-01-01','yyyy-mm-dd')
group by mbrid)
where s=0
and mi <=to_date('2005-01-01','yyyy-mm-dd')
and ma>=to_date('2005-12-31','yyyy-mm-dd')
这个是你以前写的那个代码…… 你看看对这个有没有什么作用
select distinct mbrid from(
select mbrid,expdat,expdat-effdat a,effdat-lag(effdat)over(partition by mbrid order by effdat)b from
(
select mbrid,effdat,lead(effdat)over(partition by mbrid order by effdat)expdat from(
select mbrid,effdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'
union
select mbrid,expdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'))a
where expdat is not null
and not exists(select 1 from test_teTs where
a.mbrid=mbrid and a.effdat>=effdat and a.expdat<=expdat
and effdat between date'2005-1-1' and date'2005-12-31')
and expdat-effdat>1)
where a>2 or b<45
我刚才弄了部分的数据进行测试 有这样的数据
MBRID EFFDAT EXPDAT
1000002 2001-4-1 2001-7-1
1000002 2001-7-1 2002-2-1
1000002 2002-1-1 2002-1-31
但是这个程序没有检索出来啊? 正常这个人在2005-1-1 到 2006-1-1之内都没有登记,就更不用说连续了
你看是错误出在了什么地方啊
可以根据全部用户id列表找出2005年有记录且不在上面查询结果中的id,就是有登记且连续的吧
我想想有没有办法优化下
select distinct mbrid from test_tets t where not exists(
select 1 from(
select mbrid,expdat,expdat-effdat a,effdat-lag(effdat)over(partition by mbrid order by effdat)b from
(
select mbrid,effdat,lead(effdat)over(partition by mbrid order by effdat)expdat from(
select mbrid,effdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'
union
select mbrid,expdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'))a
where expdat is not null
and not exists(select 1 from test_teTs where
a.mbrid=mbrid and a.effdat>=effdat and a.expdat<=expdat
and effdat between date'2005-1-1' and date'2005-12-31')
and expdat-effdat>1)
where a>2 or b<45
and mbrid=t.mbrid)
and effdat between date'2005-1-1' and date'2005-12-31'
因为
MBRID EFFDAT EXPDAT
-32169 2005-6-14 2005-8-31
-32169 2005-7-1 2005-8-31
-32169 2005-9-1 2005-12-31
-32169 2006-1-1 2006-3-31
-32169 2006-1-1 2006-8-31
-32169 2006-9-1 2006-9-30
-32169 2006-10-1 2006-12-31
这样的数据也出现在了查询的结果中 但是他不是合格的啊
现在查询的结果必须是从2005-1-1 到2006-1-1 之间都是连续的!就是从一月一号 到十二月三十一号都是连续的啊?
select mbrid from test_tets t where not exists(
select 1 from(
select mbrid,expdat,expdat-effdat a,effdat-lag(effdat)over(partition by mbrid order by effdat)b from
(
select mbrid,effdat,lead(effdat)over(partition by mbrid order by effdat)expdat from(
select mbrid,effdat from test_teTs where effdat<=date'2006-1-1' and expdat>=date'2005-1-1'
union
select mbrid,expdat from test_teTs where effdat<=date'2006-1-1' and expdat>=date'2005-1-1'))a
where expdat is not null
and not exists(select 1 from test_teTs where
a.mbrid=mbrid and a.effdat>=effdat and a.expdat<=expdat
and effdat<=date'2006-1-1' and expdat>=date'2005-1-1')
and expdat-effdat>1)
where a>2 or b<45
and mbrid=t.mbrid)
and effdat<=date'2006-1-1' and expdat>=date'2005-1-1'
group by mbrid
having max(expdat)>=date'2005-12-31' and min(effdat)<=date'2005-1-1'
[email protected]
发到我邮箱吧
你看一下 我的表里面有15969835条数据
下面这个是执行计划SELECT STATEMENT, GOAL = ALL_ROWS Cost=312148008697 Cardinality=2460 Bytes=51660 Time=3745776105
FILTER
HASH GROUP BY Cost=312148008697 Cardinality=2460 Bytes=51660 Time=3745776105
FILTER
TABLE ACCESS FULL Object owner=ZJHIT Object name=MBR_HIST Cost=17222 Cardinality=2876408 Bytes=60404568 Time=207
VIEW Object owner=ZJHIT Cost=109118 Cardinality=287641 Bytes=4889897 Time=1310
WINDOW BUFFER Cost=109118 Cardinality=287641 Bytes=6615743 Time=1310
FILTER
VIEW Object owner=ZJHIT Cost=101630 Cardinality=5752816 Bytes=132314768 Time=1220
WINDOW SORT Cost=101630 Cardinality=5752816 Bytes=80539424 Time=1220
VIEW Object owner=ZJHIT Cost=72317 Cardinality=5752816 Bytes=80539424 Time=868
SORT UNIQUE Cost=72317 Cardinality=5752816 Bytes=120809136 Time=868
UNION-ALL
TABLE ACCESS FULL Object owner=ZJHIT Object name=MBR_HIST Cost=17167 Cardinality=2876408 Bytes=60404568 Time=207
TABLE ACCESS FULL Object owner=ZJHIT Object name=MBR_HIST Cost=17167 Cardinality=2876408 Bytes=60404568 Time=207
TABLE ACCESS BY INDEX ROWID Object owner=ZJHIT Object name=MBR_HIST Cost=7488 Cardinality=1 Bytes=21 Time=90
INDEX RANGE SCAN Object owner=ZJHIT Object name=HIST2 Cost=390 Cardinality=28746 Time=5
效率太低了 看看怎么还能优化一下啊?现在的效率太低……
上面测试数据不出结果问题应该在这里:a>2 or b<45外面忘记加一层括号了(a>2 or b<45)
select mbrid from(
select mbrid,effdat,expdat,case flag1 when 0 then
effdat-lag(effdat)over(partition by mbrid,flag1 order by effdat) end bt from(
select mbrid,effdat,expdat
,min(
case when effdat<=date'2005-1-1' or exists (select 1 from TEST_MBR_HIST
where mbrid=a.mbrid and effdat<a.effdat and expdat>a.effdat-2) then 1 --连续
when exists (select 1 from test_mbr_hist
where mbrid=a.mbrid and effdat<a.effdat and expdat=a.effdat-2) then 0 --隔2天
else -1 --cut
end )over(partition by mbrid)flag,
case when effdat-lag(expdat)over(partition by mbrid order by effdat)=2 then 0 end flag1
from (
select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST
where effdat<date'2006-1-1'
and expdat>=date'2005-1-1'
group by mbrid ,effdat)a
)where flag>=0)
group by mbrid having (min(bt)>45 or min(bt) is null)and max(expdat)>=date'2005-12-31'
你可以将(
select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST
where effdat<date'2006-1-1'
and expdat>=date'2005-1-1'
group by mbrid ,effdat)a
这个结果先提取出来放在临时表里
再根据这个临时表来进行后续的查询
create global temporary table a on commit preserve rows as
select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST
where effdat<date'2006-1-1'
and expdat>=date'2005-1-1'
group by mbrid ,effdat;
select mbrid from(
select mbrid,expdat,case when flag=2 then effdat-lag( effdat)over(partition by mbrid,flag order by effdat) end flag1 from ( select mbrid,effdat,expdat,effdat-prior expdat flag from( select mbrid,effdat,expdat,row_number()over(partition by mbrid order by effdat,expdat)rn from(
select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST
where effdat<date'2006-1-1' and expdat>date'2005-1-1'
group by mbrid,effdat))t
start with rn=1 and effdat<=date'2005-1-1' connect by rn>prior rn
and mbrid=prior mbrid
and expdat>prior expdat
and effdat<=prior expdat+2))
group by mbrid
having max(expdat)>=date'2005-12-31'
and (min(flag1)>=45 or min(flag1) is null)
p_mbrid VARCHAR2
, p_start_date DATE
, p_end_date DATE)
RETURN VARCHAR2 IS
w_ret VARCHAR2 (1);
BEGIN
SELECT 'Y'
INTO w_ret
FROM (SELECT expdat, expdat - effdat a
, effdat - LAG (effdat) OVER (ORDER BY effdat) b
FROM (SELECT effdat, LEAD (effdat) OVER (ORDER BY effdat) expdat
FROM (SELECT effdat
FROM test_tets
WHERE effdat <= p_end_date
AND expdat >= p_start_date
AND mbrid = p_mbrid
UNION
SELECT expdat
FROM test_tets
WHERE effdat <= p_end_date
AND expdat >= p_start_date
AND mbrid = p_mbrid
UNION
SELECT p_start_date
FROM DUAL
UNION
SELECT p_end_date
FROM DUAL)) a
WHERE expdat IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM test_tets
WHERE mbrid = p_mbrid
AND a.effdat >= effdat
AND a.expdat <= expdat
AND effdat <= p_end_date
AND expdat >= p_start_date)
AND expdat - effdat > 1)
WHERE (a > 2 OR b < 45);
RETURN w_ret;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'N';
END date_gap_exists;
/查询时使用如下,test_mbrid是我建的一个测试表,包含所有mbridSELECT mbrid
, date_gap_exists (mbrid
, TO_DATE ('2005-01-01', 'YYYY-MM-DD')
, TO_DATE ('2006-01-01', 'YYYY-MM-DD')) check_date_gap
FROM test_mbrid;
你好,这个函数好像是有问题,当我执行我的测试数据的时候。有这样的一组数据EFFDAT EXPDAT MBRID
2006-2-16 2006-3-31 -36730
2006-4-1 2006-8-6 -36730
2006-11-1 2006-12-31 -36730
当这个函数执行这个mbrid的时候就报出实际返回的行数超出请求的行数。你看看是怎么回事啊?
它为什么返回了两个‘Y’啊? 是什么问题啊?
p_mbrid VARCHAR2
, p_start_date DATE
, p_end_date DATE)
RETURN VARCHAR2 IS
w_ret VARCHAR2 (1);
BEGIN
SELECT 'Y'
INTO w_ret
FROM (SELECT expdat, expdat - effdat a
, effdat - LAG (effdat) OVER (ORDER BY effdat) b
FROM (SELECT effdat, LEAD (effdat) OVER (ORDER BY effdat) expdat
FROM (SELECT effdat
FROM test_tets
WHERE effdat <= p_end_date
AND expdat >= p_start_date
AND mbrid = p_mbrid
UNION
SELECT expdat
FROM test_tets
WHERE effdat <= p_end_date
AND expdat >= p_start_date
AND mbrid = p_mbrid
UNION
SELECT p_start_date
FROM DUAL
UNION
SELECT p_end_date
FROM DUAL)) a
WHERE expdat IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM test_tets
WHERE mbrid = p_mbrid
AND a.effdat >= effdat
AND a.expdat <= expdat
AND effdat <= p_end_date
AND expdat >= p_start_date)
AND expdat - effdat > 1)
WHERE (a > 2 OR b < 45)
AND rownum = 1; -- 加上此行避免重复
RETURN w_ret;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'N';
END date_gap_exists;
/
但是我的测试数据EFFDAT EXPDAT MBRID
2006-2-16 2006-3-31 -36730
2006-4-1 2006-8-6 -36730
2006-11-1 2006-12-31 -36730
在06-07年期间返回的也是Y 就是说经过判断这个也是连续的了……