22,209
社区成员
发帖
与我相关
我的任务
分享
--建立测试环境
IF OBJECT_ID('tb1') IS NOT NULL DROP TABLE tb1
GO
CREATE TABLE tb1
(
FLAG varchar(10),
VALUE int
)
GO
INSERT TB1
SELECT 'A' , 2 union all
SELECT 'B' , 3 union all
SELECT 'C' , 5 union all
SELECT 'D' , 4
go
IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
GO
CREATE TABLE tb2
(
FLAG varchar(10),
COMP varchar(20)
)
GO
INSERT TB2
SELECT 'Loa' , 'A*B' union all
SELECT 'BWC' , 'Loa*D' union all
SELECT 'ABC', 'BWC*C' union all
SELECT 'ND' ,'ABC*BWC'
go
--查询
IF OBJECT_ID('f_test') IS NOT NULL DROP function f_test
GO
create function f_test(@s varchar(20))
returns varchar(20)
as
begin
while exists(select 1 from tb2 where charindex(FLAG,@s)>0)
select @s=replace(@s,FLAG,COMP) from tb2 where charindex(FLAG,@s)>0
select @s=replace(@s,FLAG,VALUE) from tb1 where charindex(FLAG,@s)>0
return @s
end
go
declare @s varchar(8000)
select @s=isnull(@s+' union all ','')+'select '''+FLAG+''' as [flag],'+dbo.f_test(COMP)+' as comp' from tb2
exec(@s)
--结果
/*
flag comp
---- -----------
Loa 6
BWC 24
ABC 120
ND 2880
*/