SQL如何将正确的日期分配给连续日期列中的多个null

Question

我有一系列员工的连续日期，中间有null。处理null的逻辑如下所述：

如果todate为null，且fromdate的下一条记录有值，则todate为(fromdate-1的下一个记录(
如果today为null，并且fromdate没有下一条纪录，则将todate默认为2020年8月1日

当前数据

所需数据

Answer 1

这应该会让你开始，但由于你没有说明你正在使用哪些dbms，所以扣一天的语法会有很多变化。

CREATE TABLE mytable(
FROMDATE DATE  NOT NULL
,TODATE   DATE 
);
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2016','30/04/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/05/2016',NULL);
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('2/06/2016','16/10/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('17/10/2016','31/12/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2017','14/02/2017');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2017','31/12/2017');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2018','14/02/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2018','20/02/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('21/02/2018','31/03/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/04/2018','31/12/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2019','31/03/2019');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/04/2019','31/12/2019');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2020',NULL);
select
fromdate
, todate                                                        as todate_raw
, coalesce(todate,lag(fromdate,1) over(order by fromdate DESC)) as todate_new
from mytable
order by fromdate

fromdate|todate_raw|todate_new：--------|：---------|：---------2016-02-15 | 2016-04-30 | 2016-04-312016-05-01 |null|2016-06-022016-06-02|2016-10-16|2016-10-162016-10-17|2016-12-31|2016-12-312017-01-01|2017-02-14|2017-02-142017-02-15|2017-12-31|2017-12-312018-01-01|2018-02-14|2018-02-142018-02-15|2018-02-20|2018-02-202018-02-21 | 2018-03-31 | 2018-03/312018-04-01 | 2018-12-31 | 2018-12/312019-01-01|2019-03-31|2019-03-312019-04-01|2019-12-31|2019-22-312020-01-01|null|null

db<gt；小提琴这里