SQL 显示前导、滞后记录的键

debugcn 发表于 Dev

苏拉布

我在表中有数据可以通过 SQL 呈现如下：

SELECT T.VERSION_ID T_VERSION_ID
                ,cast(T.START_DATE As Date) as T_START_DATE
                ,cast(ISNULL( LEAD (START_DATE) OVER (ORDER BY START_DATE),'9999-12-31') As Date) as CALC_END_DATE_LEAD
                ,cast(ISNULL( LAG  (START_DATE) OVER (ORDER BY START_DATE),'9999-12-31') As Date) as CALC_END_DATE_LAG
FROM(select 'Vrandom1' as VERSION_ID
            ,cast('22-MAR-2018' As Date) as start_date
            ,'9999-12-31' as end_date
            , 1 as is_approved
    union
    select 'Vrandom2' as VERSION_ID
           ,cast('28-MAR-2018' As Date) as start_date
           ,'9999-12-31' as end_date
           ,1 as is_approved
    union
    select 'Vrandom3' as VERSION_ID
          ,cast('25-MAR-2018' As date) as start_date
          ,'9999-12-31' as end_date
          ,1 as is_approved
    ) as T

输出

T_VERSION_ID  T_START_DATE CALC_END_DATE_LEAD                CALC_END_DATE_LAG

Vrandom1                           22/03/2018                         25/03/2018                                         31/12/9999
Vrandom3                           25/03/2018                         28/03/2018                                         22/03/2018
Vrandom2                           28/03/2018                         31/12/9999                                         25/03/2018

此表在应用程序中使用，其中一条记录说版本“Vrandom3”将生效。对于处理，我需要根据开始日期查找即时领先和落后记录的键。即我需要显示 Vrandom2 和 Vrandom1 作为领先和落后记录的键。

应用中的期望结果：

T_VERSION_ID  T_START_DATE    CALC_END_DATE_LEAD   CALC_END_DATE_LAG   key_leading   key_lagging

Vrandom3      25/03/2018      28/03/2018           22/03/2018          Vrandom2      Vrandom1

或者

T_VERSION_ID  T_START_DATE   CALC_END_DATE_LEAD   CALC_END_DATE_LAG   key_leading   key_lagging

Vrandom1      22/03/2018     25/03/2018           31/12/9999          Vrandom3      null

我可以考虑加入基于 start_date 的内联视图，但有没有更好的方法来实现这一点？

用户1443098

如何添加：

,LEAD (key_col) OVER (ORDER BY START_DATE),'9999-12-31') As Date) as Key_col_LEAD
,LAG (key_col) OVER (ORDER BY START_DATE),'9999-12-31') As Date) as Key_col_LAG

到您的选择

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。