將員工名稱更新到部門員工表里的人員名稱里
uj5u.com熱心網友回復：
split，然后cross, for xmluj5u.com熱心網友回復：
with t as (
select 'a01' as part1,'b01/b05' as part2,'c03' as part3
union all select 'a03/a05/a07','b02/b03','c05/c06/c07'
union all select 'a02','','c04/c05'
),t1 as (
select 'a01' as code,'張三' as name
union all select 'a02','李四'
union all select 'a03','王五'
union all select 'a05','趙六'
)
select * from (
select *,(len(part1)+1)/4 as p1,(len(part2)+1)/4 as p2,(len(part3)+1)/4 as p3
from t
) a
cross apply (
select stuff(isnull((
select '/'+isnull(b.name,'')
from master..spt_values x
left join t1 b on substring(a.part1,x.number*4+1,3)=b.code
where type='p' and number<p1
for xml path ('')),''),1,1,'') as name1
) b
如果確定code長度一致，就可以用substring來搞
uj5u.com熱心網友回復：
--
declare @t2 table (part1 varchar(1000),part2 varchar(1000),part3 varchar(1000))
insert @t2
select 'a01' as part1,'b01/b05' as part2,'c03' as part3
union all select 'a03/a05/a07','b02/b03','c05/c06/c07'
union all select 'a02','','c04/c05'
declare @t1 table (code varchar(1000),name varchar(1000))
insert @t1
select 'a01' as code,'張三' as name
union all select 'a02','李四'
union all select 'a03','王五'
union all select 'a05','趙六'
union all select 'a07','aaaa'
union all select 'b01','趙六'
union all select 'b02','aaaa'
union all select 'c04','趙六'
union all select 'c05','aaaa'
--直接轉換查詢結果
select part1 ,
stuff((select '/'+name from @t1 where patindex('%/'+code+'%','/'+part1) > 0 for xml path('')),1,1,''),
part2,
stuff((select '/'+name from @t1 where patindex('%/'+code+'%','/'+part2) > 0 for xml path('')),1,1,''),
part3,
stuff((select '/'+name from @t1 where patindex('%/'+code+'%','/'+part3) > 0 for xml path('')),1,1,'')
from @t2
uj5u.com熱心網友回復：
我感覺這個提問的內容好奇怪。部門員工表怎么來的，既然部門員工表能把員工代碼寫入進單元格里，何不直接也把員工姓名也寫進去
看格式是EXCEL 那就手動寫進去唄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/94732.html
標籤：基礎類
上一篇：求各位推薦個大學畢業設計的內容
下一篇：求一條SQLSERVER陳述句