declare @x varchar(50) while exists ( select 1 from a,b where '→'+b.des+'→' like '%→'+cast(a.id as varchar)+'→%' ) update b set @x=replace('→'+b.des+'→','→'+cast(a.id as varchar)+'→','→'+a.name+'→'), des=stuff(right(@x,len(@x)-1),1,1,'') from a,b where '→'+b.des+'→' like '%→'+cast(a.id as varchar)+'→%'
Create function get_fun(@des nvarchar(1000)) returns nvarchar(100) as begin declare @s nvarchar(1000) select @s='→'+des+'→' from b where des=@des while @@rowcount>0 select @s=replace(@s,'→'+ltrim(id)+'→','→'+ltrim(name)+'→') from a where charindex('→'+ltrim(id)+'→',@s)>0 set @s=left(@s,len(@s)-1) return right(@s,len(@s)-1) end go select des,dbo.get_fun(des) from b
declare @a table(Id int,Name varchar(10)) insert into @a select 1, 'a1' union all select 2, 'a2' union all select 3, 'a3' declare @b table([des] varchar(10)) insert into @b select '1→2' insert into @b select '2→3'select (select name from @a where id=left(b.[des],charindex('→',b.[des])-1))+ '→'+ (select name from @a where id=right(b.[des],len(b.[des])-charindex('→',b.[des]))) from @b b
create table a (id int ,name varchar(10)) insert into a select 1 ,'a' insert into a select 2 ,'b' create table b (des varchar(100)) insert into b select '1→2'alter function f_b_a(@des varchar(100)) returns varchar(1000) as begin select @des =@des+'→' declare @sql varchar(1000),@sqllast varchar(1000) select @sql = '',@sqllast ='' while charindex('→',@des,len(@sqllast)+1)>0 begin select @sql =@sql + name+'→',@sqllast = left(@des,charindex('→',@des,len(@sqllast)+1)) from a where id = substring(@des,len(@sqllast)+1,charindex('→',@des,len(@sqllast))-1) end return left(@sql,len(@sql)-1) end select dbo.f_b_a(des) as b from b b ------------------------------------------ a→b感觉这样比鸟儿的快写...
while exists (
select 1
from a,b
where '→'+b.des+'→' like '%→'+cast(a.id as varchar)+'→%'
)
update b
set @x=replace('→'+b.des+'→','→'+cast(a.id as varchar)+'→','→'+a.name+'→'),
des=stuff(right(@x,len(@x)-1),1,1,'')
from a,b
where '→'+b.des+'→' like '%→'+cast(a.id as varchar)+'→%'
returns nvarchar(100)
as
begin
declare @s nvarchar(1000)
select @s='→'+des+'→' from b where des=@des
while @@rowcount>0
select @s=replace(@s,'→'+ltrim(id)+'→','→'+ltrim(name)+'→') from a where charindex('→'+ltrim(id)+'→',@s)>0
set @s=left(@s,len(@s)-1)
return right(@s,len(@s)-1)
end
go
select des,dbo.get_fun(des) from b
insert into @a
select 1, 'a1' union all
select 2, 'a2' union all
select 3, 'a3' declare @b table([des] varchar(10))
insert into @b select '1→2'
insert into @b select '2→3'select
(select name from @a where id=left(b.[des],charindex('→',b.[des])-1))+
'→'+
(select name from @a where id=right(b.[des],len(b.[des])-charindex('→',b.[des])))
from @b b
create table a (id int ,name varchar(10))
insert into a select 1 ,'a'
insert into a select 2 ,'b'
create table b (des varchar(100))
insert into b select '1→2'alter function f_b_a(@des varchar(100))
returns varchar(1000)
as begin
select @des =@des+'→'
declare @sql varchar(1000),@sqllast varchar(1000)
select @sql = '',@sqllast =''
while charindex('→',@des,len(@sqllast)+1)>0
begin
select @sql =@sql + name+'→',@sqllast = left(@des,charindex('→',@des,len(@sqllast)+1)) from a
where id = substring(@des,len(@sqllast)+1,charindex('→',@des,len(@sqllast))-1)
end
return left(@sql,len(@sql)-1)
end select dbo.f_b_a(des) as b
from b b
------------------------------------------
a→b感觉这样比鸟儿的快写...
---
不太明白什么意思,哪两列呢。a表是一个作业步骤表,b表是一个流程表。a表里面存的是吃饭,跑步,泡妞,睡觉这样的小作业流程。
b表是一个大的预定好的流程,比如定义了8.2号做如下事情:吃饭→跑步→睡觉
但8.3号为了泡妞不用跑步了,就成了吃饭→泡妞→睡觉我也没想出其他好办法,大家遇到类似问题都如何做的。对付可变的业务流程