27,579
社区成员
发帖
与我相关
我的任务
分享
declare @tb table (iID int, cCode nvarchar(5),cText nvarchar(10))
insert into @tb select 3,'0001','套件'
union all select 4 ,null,'组件'
union all select 7, null,'组件'
union all select 8, '0002', '套件'
union all select 9,null,'组件'
union all select 19, '0003', '套件'
union all select 34,null,'组件'
union all select 54,null,'组件'
update @tb set ccode=(select ccode from @tb where iid=(select max(iid) from @tb where iID<a.iID and ccode is not null))
from @tb a where a.ccode is null
select * from @tb
declare @tb table (iID int, cCode nvarchar(5),cText nvarchar(10))
insert into @tb select 3,'0001','套件'
union all select 4 ,null,'组件'
union all select 7, null,'组件'
union all select 8, '0002', '套件'
union all select 9,null,'组件'
union all select 19, '0003', '套件'
union all select 34,null,'组件'
union all select 54,null,'组件'
--select * from @tb
select iID,
case when cCode is not null then cCode else ( select max(cCode) from @tb where cText ='套件' and iID < t.iID and cText <> t.cText ) end as cCode,
cText from @tb t
/*
(8 row(s) affected)
iID cCode cText
----------- ----- ----------
3 0001 套件
4 0001 组件
7 0001 组件
8 0002 套件
9 0002 组件
19 0003 套件
34 0003 组件
54 0003 组件
(8 row(s) affected)
*/