我的表格中有以下数据.
- | 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
