sql – 如何获取空值的先前值

我的表格中有以下数据.

| Id  |  FeeModeId   |Name        |   Amount|
   ---------------------------------------------
   | 1   |  NULL        | NULL       |   20    |
   | 2   |  1           | Quarter-1  |   5000  |
   | 3   |  NULL        | NULL       |   2000  |    
   | 4   |  2           | Quarter-2  |   8000  |
   | 5   |  NULL        | NULL       |   5000  |
   | 6   |  NULL        | NULL       |   2000  |
   | 7   |  3           | Quarter-3  |   6000  |
   | 8   |  NULL        | NULL       |   4000  |

如何编写这样的查询以获得低于输出…

| Id  |  FeeModeId   |Name        |   Amount|
   ---------------------------------------------
   | 1   |  NULL        | NULL       |   20    |
   | 2   |  1           | Quarter-1  |   5000  |
   | 3   |  1           | Quarter-1  |   2000  |    
   | 4   |  2           | Quarter-2  |   8000  |
   | 5   |  2           | Quarter-2  |   5000  |
   | 6   |  2           | Quarter-2  |   2000  |
   | 7   |  3           | Quarter-3  |   6000  |
   | 8   |  3           | Quarter-3  |   4000  |

解决方法

select 
    a.ID,ISNULL(a.FeeModeId,x.FeeModeId) FeeModeId,ISNULL(a.Name,x.Name) Name,a.Amount
from tbl a
outer apply
(select top 1 FeeModeId,Name 
    from tbl b 
    where b.ID<a.ID and 
        b.Amount is not null and 
        b.FeeModeId is not null and 
        a.FeeModeId is null order by ID desc)x

要么

select 
    ID,ISNULL(FeeModeId,bFeeModeId) FeeModeId,ISNULL(Name,bName) Name,Amount
From(
    select 
        a.ID,a.FeeModeId,a.Name,a.Amount,b.ID bID,b.FeeModeId bFeeModeId,b.Name bName,MAX(b.FeeModeId) over (partition by a.ID) mx
    from tbl a left join tbl b on b.ID<a.ID
    and b.FeeModeId is not null
)x 
where bFeeModeId=mx or mx is null

（编辑：源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!