22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (FLAG varchar(1),VALUE1 int,VALUE2 int)
insert into [ta]
select 'A',2,3 union all
select 'B',3,5 union all
select 'C',5,6 union all
select 'D',4,8
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (FLAG varchar(3),EXPRESS varchar(17))
insert into [tb]
select 'Loa','A*B' union all
select 'NO','#VALUE-VALUE1' union all
select 'BWC','Loa*D' union all
select 'ABC','BWC*C' union all
select 'ND','ABC*BWC'
-- 辅助表
----------------------------------------------
if object_id('#T') is not null drop table #T
go
create table #T(FLAG varchar(3) ,value1 int,value2 int)
declare @id varchar(10),@s nvarchar(500),@S1 nVARCHAR(500) ,@Nn INT,@Nn1 INT
--游标
declare cur cursor for select flag,express from tb
open cur
fetch next from cur into @id, @s
while @@fetch_status=0
begin
SET @S1=@S
--------------新增07.27--------------
if charindex('#VALUE',@S)>0
BEGIN
SELECT @S='0'
SELECT @S1=LTRIM(VALUE2-VALUE1) FROM TA WHERE FlaG='B'
END
-------------------------------------
select @s1=replace(@s1,flag,value2) from #T
SELECT @s=replace(@s,flag,value1) from #T
select @s1=replace(@s1,flag,value2) from ta
SELECT @s=replace(@s,flag,value1) from ta
if charindex('IF(',@S)>0
BEGIN
SET @S= REPLACE(REPLACE(REPLACE(STUFF(@S,CHARINDEX(',',@S),1,' THEN '),'IF(','CASE WHEN '),',',' ELSE '),')','')+' END'
SET @S1= REPLACE(REPLACE(REPLACE(STUFF(@S1,CHARINDEX(',',@S1),1,' THEN '),'IF(','CASE WHEN '),',',' ELSE '),')','')+' END'
END
SELECT @S=N'SELECT @N='+@S,@S1='SELECT @n1='+@S1
--PRINT @S
--PRINT @S1
EXEC sp_executesql @S,N'@n int output',@nn output
EXEC sp_executesql @S1,N'@n1 int output',@nn1 output
insert into #t select @id,@nn,@nn1
fetch next from cur into @id, @s
end
close cur
deallocate cur
-- 结果
------------------------
SELECT * FROM #T
FLAG value1 value2
---- ----------- -----------
Loa 6 15
NO 0 2
BWC 24 120
ABC 120 720
ND 2880 86400
(5 行受影响)