sql中Firebird 查询中包含下一行的值

我有一个包含以下列的表格(非常简化):

CustomerID | ValidFrom | ValidTo 
---------------------------------- 
1          | 20180101  | 20180330 
2          | 20180601  | 20181003 
1          | 20180212  | 20180313 

我发现一个查询可以实现以下结果:

CustomerID | ValidFrom 
----------------------- 
1          | 20180101 
1          | 20180212 
1          | 20180314 
1          | 20180331 
2          | 20180601    
2          | 20181004 

查询:

SELECT CustomerID, ValidFrom 
FROM table 
UNION 
SELECT CustomerID, ValidTo + 1 
FROM table 

但是我怎样才能实现以下结果(几乎是上一行中包含的下一行的值)？

CustomerID | ValidFrom | ValidTo 
--------------------------------- 
1          | 20180101  | 20180211 
1          | 20180212  | 20180313 
1          | 20180314  | 20180330 
1          | 20180331  | NULL 
2          | 20180601  | 20181003 
2          | 20181004  | NULL 

非常感谢!

请您参考如下方法:

您可以使用 SQL window functions 获取第二个查询的结果:

select 
    validfrom 
  , lag(validfrom,1) over (partition by customerId, order by validFrom) 
  from ( 
      SELECT CustomerID, ValidFrom 
      FROM table 
      UNION 
      SELECT CustomerID, ValidTo + 1 
      FROM table 
  ) foo