本帖最后由 tigeryangjin 于 2013-9-13 01:13 编辑
大家好!
我这里碰到一个问题:有一个表,假设为table_a,它有5列:create table table_a (
row_wid number(10),
item varchar2(25),
loc number(10),
effective_from_dt date,
effective_to_dt date);
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (4,'800188132',120135, date'2011-01-28',date'2013-05-06');
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (7,'800188132',120135, date'2011-04-18',date'2013-05-06');
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (5,'800188132',120135, date'2011-05-23',date'2013-05-06');
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (8,'800188132',120135, date'2011-06-11',date'2013-05-06');
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (1,'100622763',120086, date'2011-02-05',date'2013-05-06');
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (6,'100622763',120086, date'2011-03-11',date'2013-05-06');
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (2,'100622763',120086, date'2011-05-09',date'2013-05-06');
insert into table_a (row_wid,item,loc,effective_from_dt,effective_to_dt) values (3,'100622763',120086, date'2011-05-12',date'2013-05-06');
row_wid为主键,Sequences序列生成。其中item和loc组合确定一个实体,表中拥有多个实体,同一个实体有多条记录,但是现在数据有点问题:effective_to_dt日期不对。正确的应该是同一个实体(item,loc)的effective_to_dt日期为下一条的effective_from_dt前一天:
SELECT * FROM TABLE_A T ORDER BY T.ITEM,T.LOC,T.EFFECTIVE_FROM_DT;
ROW_WID ITEM LOC EFFECTIVE_FROM_DT EFFECTIVE_TO_DT
4 800188132 120135 2011-1-28 2013-4-17
7 800188132 120135 2011-4-18 2013-5-22
5 800188132 120135 2011-5-23 2013-6-10
8 800188132 120135 2011-6-11 2013-7-6
1 100622763 120086 2011-2-5 2013-3-10
6 100622763 120086 2011-3-11 2013-5-8
2 100622763 120086 2011-5-9 2013-5-11
3 100622763 120086 2011-5-12 2013-7-6
也就是说,同一个实体的N条记录的effective_from_dt 和effective_to_dt 没有交叉的日期,请问语句要如何实现?