有两个表一个用于存数据,另一表用于存公式,
例如表tb如下
NO VALUE
A 2有另一表fc
NO EXPRESS
LM CASE(A,(1,2,3,4),(2,4,6,8),default 0)
*意思是如果A为1,LM就是2,A为2,LM就是4,A为3,LM就是6,如果A不是在1,2,3,4范围内,就默认为0.自定义一个case函数,解析该公式求出LM的值
例如表tb如下
NO VALUE
A 2有另一表fc
NO EXPRESS
LM CASE(A,(1,2,3,4),(2,4,6,8),default 0)
*意思是如果A为1,LM就是2,A为2,LM就是4,A为3,LM就是6,如果A不是在1,2,3,4范围内,就默认为0.自定义一个case函数,解析该公式求出LM的值
if object_id('TA') is not null drop table TA
GO
create table TA (NO varchar(1),VALUE int)
insert into TA
select 'A',2if object_id('fc') is not null drop table fc
GO
create table fc (NO VARCHAR(10),EXPRESS VARCHAR(50))
insert into fc
SELECT 'LM','CASE(A,(1,2,3,4),(2,4,6,8),default 0)' ----------------------------------------------------------
if object_id('TEMPDB..#T') is not null drop table #T
go
create table #T(NO varchar(3) ,value1 int )
declare @id varchar(10),@s nvarchar(500),@Nn INT,@s1 varchar(50),@s2 varchar(50),@s3 varchar(50),@s4 varchar(50)--游标
declare cur cursor for select NO,express from FC
open cur
fetch next from cur into @id, @swhile @@fetch_status=0
begin
SELECT @s=replace(@s,NO+',',LTRIM(value)+',') from ta ---替换值
select @s= REPLACE(REPLACE(replace(replace(replace(@s,',(','.('),'),',').'),'CASE(',''),'(',''),')','') ---替换@s中的 ,( ),分别为 .( ).
SELECT @S1=PARSENAME(@S,4),@S2=REPLACE(PARSENAME(@S,3),',','.'),@S3=REPLACE(PARSENAME(@S,2),',','.'),@S4=PARSENAME(@S,1)
SELECT @S=CASE @S1 WHEN PARSENAME(@S2,1) THEN PARSENAME(@S3,1)
WHEN PARSENAME(@S2,2) THEN PARSENAME(@S3,2)
WHEN PARSENAME(@S2,3) THEN PARSENAME(@S3,3)
WHEN PARSENAME(@S2,4) THEN PARSENAME(@S3,4)
ELSE SUBSTRING(@S4,CHARINDEX(' ',@S4)+1,LEN(@S4)-CHARINDEX(' ',@S4)) END
insert into #t select @id,@S fetch next from cur into @id, @s
end
close cur
deallocate cur -- 结果
------------------------SELECT * FROM #T
NO value1
---- -----------
LM 4(1 行受影响)
set @input = 4declare @EXPRESS varchar(200)
set @EXPRESS = 'CASE(A,(1,2,3,4),(2,4,6,8),default 0)'----------以上参数作为函数的输入参数, 根据tb.NO应该可以匹配出fc.EXPRESSdeclare @return intdeclare @v varchar(100), @r varchar(100), @d varchar(10), @p1 int, @p2 int
select @p1 = charindex('(', @EXPRESS), @p2 = charindex(')',@EXPRESS)set @p1 = charindex('(', @EXPRESS, @p1+1)
set @v = substring(@EXPRESS, @p1+1, @p2-@p1-1)+','select @p1 = charindex('(', @EXPRESS, @p1+1), @p2 = charindex(')', @EXPRESS, @p2+1)
select @r = substring(@EXPRESS, @p1+1, @p2-@p1-1)+','declare @tmp varchar(10), @index int, @max int, @i int
select @index = 1, @max = len(@v)-len(replace(@v,',','')), @i = 1while charindex(',',@v)>0
begin
set @tmp = left(@v, charindex(',',@v)-1)
if @tmp = @input break
set @index = @index + 1
set @v = stuff(@v, 1, charindex(',',@v), '')
endif @index > @max
begin
set @p1 = charindex('default', @EXPRESS)
set @return = substring(@EXPRESS, @p1+8, len(@EXPRESS)-@p1-8)
endelse
begin
while @i<@index
begin
set @r = stuff(@r, 1, charindex(',',@r), '')
set @i = @i + 1
end
set @return = left(@r, charindex(',',@r)-1)
endselect @return --> 8
declare @input int
set @input = 2declare @EXPRESS varchar(200)
set @EXPRESS = 'CASE(A,(1,2,3,4),(2,4,6,8),default 0)'----------以上参数作为函数的输入参数, 根据tb.NO应该可以匹配出fc.EXPRESSdeclare @return intdeclare @v varchar(100), @r varchar(100), @d varchar(10), @p1 int, @p2 int
select @p1 = charindex('(', @EXPRESS), @p2 = charindex(')',@EXPRESS)set @p1 = charindex('(', @EXPRESS, @p1+1)
set @v = substring(@EXPRESS, @p1+1, @p2-@p1-1)select @p1 = charindex('(', @EXPRESS, @p1+1), @p2 = charindex(')', @EXPRESS, @p2+1)
select @r = substring(@EXPRESS, @p1+1, @p2-@p1-1);with vp (n, p1, p2) as
(
select 1, charindex(',', ','+@v), charindex(',', @v+',') + 1 where charindex(',', @v) > 0
union all
select n+1, p2, charindex(',', @v+',', p2) + 1 from vp where charindex(',', @v+',', p2) > 0
),
v as
(
select n, substring(@v, p1, p2-p1-1)v from vp
),
rp (n, p1, p2) as
(
select 1, charindex(',', ','+@r), charindex(',', @r+',') + 1 where charindex(',', @r) > 0
union all
select n+1, p2, charindex(',', @r+',', p2) + 1 from rp where charindex(',', @r+',', p2) > 0
),
r as
(
select n, substring(@r, p1, p2-p1-1)r from rp
)
select @return = r.r from v inner join r on r.n = v.n where v.v = @Inputif @return is null
begin
set @p1 = charindex('default', @EXPRESS)
set @return = substring(@EXPRESS, @p1+8, len(@EXPRESS)-@p1-8)
endselect @return -- 4