startDate endDate number
2006-09-01 1
2007-04-01 1
2007-06-01 1
2007-08-01 1
2007-09-01 1
2007-10-01 1
2009-03-23 2
2009-04-25 2现在有个表A,有两三个字段,开始日期和结束日期以及编码,其中开始日期都有值,但是结束日期都是空。有什么办法可以把结束日期更新到下一条记录开始日期的前一天啊。更新后应该是startDate endDate number
2006-09-01 2007-03-31 1
2007-04-01 2007-05-31 1
2007-06-01 2007-07-01 1
2007-08-01 2007-08-30 1
2007-09-01 null 1
2009-03-23 2009-04-24 2
2009-04-25 2009-05-24 2
2009-05-25 null 2想了很久也没找到办法,大家看看有什么办法不?谢了
2006-09-01 1
2007-04-01 1
2007-06-01 1
2007-08-01 1
2007-09-01 1
2007-10-01 1
2009-03-23 2
2009-04-25 2现在有个表A,有两三个字段,开始日期和结束日期以及编码,其中开始日期都有值,但是结束日期都是空。有什么办法可以把结束日期更新到下一条记录开始日期的前一天啊。更新后应该是startDate endDate number
2006-09-01 2007-03-31 1
2007-04-01 2007-05-31 1
2007-06-01 2007-07-01 1
2007-08-01 2007-08-30 1
2007-09-01 null 1
2009-03-23 2009-04-24 2
2009-04-25 2009-05-24 2
2009-05-25 null 2想了很久也没找到办法,大家看看有什么办法不?谢了
你的办法可能不行。关键是要取到下一条记录的开始日期,然后-1,然后赋值到当前记录的enddate, 如果是一个编码的最后一个记录,就直接设置成null。难就难在这了,大家再看看哦
create table a(startdate date,enddate date,num int);
insert into a values(to_date('2006-09-01','yyyy-mm-dd'),null,1);
insert into a values(to_date('2007-04-01','yyyy-mm-dd'),null,1);
insert into a values(to_date('2007-06-01','yyyy-mm-dd'),null,1);
insert into a values(to_date('2007-09-01','yyyy-mm-dd'),null,1);
insert into a values(to_date('2007-10-01','yyyy-mm-dd'),null,1);
insert into a values(to_date('2009-03-23','yyyy-mm-dd'),null,2);
insert into a values(to_date('2009-04-25','yyyy-mm-dd'),null,2);select startdate,enddate,lead(startdate,1,null) over(order by startdate) ed from a;update a set enddate=
(select ed-1 from
(select startdate,enddate,lead(startdate,1,null) over(order by startdate) ed from a) b
where b.ed is not null and a.startdate=b.startdate)
INSERT INTO a(startdate, num) VALUES(TO_DATE('2007-04-01','YYYY-MM-DD'),1);
INSERT INTO a(startdate, num) VALUES(TO_DATE('2007-06-01','YYYY-MM-DD'),1);
INSERT INTO a(startdate, num) VALUES(TO_DATE('2007-08-01','YYYY-MM-DD'),1);
INSERT INTO a(startdate, num) VALUES(TO_DATE('2007-09-01','YYYY-MM-DD'),1);
INSERT INTO a(startdate, num) VALUES(TO_DATE('2009-03-23','YYYY-MM-DD'),2);
INSERT INTO a(startdate, num) VALUES(TO_DATE('2009-04-25','YYYY-MM-DD'),2);
INSERT INTO a(startdate, num) VALUES(TO_DATE('2009-05-25','YYYY-MM-DD'),2);
commit;
SELECT startdate, lead(startdate,1) over(partition by num order by startdate)-1 enddate,num
from a;
2 (select ed-1 from
3 (select startdate,enddate,lead(startdate,1,null) over(partition by num order by startdate) ed f
rom a) b
4 where b.ed is not null and a.startdate=b.startdate);已更新7行。SQL> commit;提交完成。SQL> select * from a;STARTDATE ENDDATE NUM
-------------- -------------- ----------
01-9月 -06 31-3月 -07 1
01-4月 -07 31-5月 -07 1
01-6月 -07 31-8月 -07 1
01-9月 -07 30-9月 -07 1
01-10月-07 1
23-3月 -09 24-4月 -09 2
25-4月 -09 2已选择7行。