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