[sql server] 一个有意思的公式计算

dengwen3

　　实际贴：http://topic.csdn.net/u/20100720/15/21f9044a-4457-4a95-bf60-018e3b4413c8.html?seed=1454200561&r=67106612#r_67106612
　　
　　--建立测试环境
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 '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'
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
--declare @rt varchar(20)
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(100)
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
*/
　　
　　
　　资料
　　
　　
　　/*-------------建立环境-----------*/
create table a
(unit_code varchar(10),item_id varchar(10),tdatetime datetime,actdata decimal(10,1))

insert a values ( '0001 ', '10 ', '2002-01-01 ',1230.0)
insert a values ( '0001 ', '1001 ', '2002-01-01 ',3341.0)
insert a values ( '0001 ', '1002 ', '2002-01-01 ',6341.0)
insert a values ( '0001 ', '100101 ', '2002-01-01 ',619168.0)
insert a values ( '0001 ', '100102 ', '2002-01-01 ',619.0)
insert a values ( '0001 ', '100110 ', '2002-01-01 ',2230.0)
insert a values ( '0001 ', '100111 ', '2002-01-01 ',34311.0)
insert a values ( '0001 ', '2001 ', '2003-01-01 ',206705280.0)
insert a values ( '0001 ', '200101 ', '2003-01-01 ',3434.2)
insert a values ( '0001 ', '20010201 ', '2003-01-01 ',842.0)
insert a values ( '0001 ', '2002 ', '2003-01-01 ',384.0)


create table b
(expressions_code varchar(10),expressions_describe varchar(10),expressions varchar(30),
gradation int)

insert b values ( '0101 ', '比率1 ', '1001/200101 ',1)
insert b values ( '0102 ', '比率2 ', '(1001-100111-100110)/200101 ',2)
insert b values ( '0103 ', '比率3 ', '(100101+100102)/200101 ',3)
insert b values ( '0104 ', '资本1 ', '1001-200101 ',4)
insert b values ( '0105 ', '资本2 ', '2002+20010201-1002 ',5)
insert b values ( '0201 ', '负债1 ', '2001/10 ',6)

create table c
(expressions_code varchar(10),expressions_end decimal(10,2))
go
/*---------建立函数来分割字符串-------------*/
create function f_split(@s varchar(8000),@split varchar(10))
returns @re table(col1 int identity(1,1),col2 varchar(100))
as
begin
declare @i int
set @i=len(@split+ 'a ')-2
while charindex(@split,@s)> 0
begin
insert into @re values (left(@s,charindex(@split,@s)-1)+ ', ')
set @s=stuff(@s,1,charindex(@split,@s)+@i, ' ')
end
insert into @re values (@s+ ', ')
return
end
go


/*--------------用游标来处理字符串中的公式计算----------------*/
declare t_cursor cursor for
select expressions_code,expressions from b
declare @sql varchar(100),@s1 varchar(800),@s2 varchar(800),@s3 decimal(10,2)
open t_cursor
fetch next from t_cursor into @s1,@s2
select @sql=@s2

select @sql=replace(@sql,a,b)
from
(select a= '( ',b= ' ' union all
select a= ') ',b= ' ' union all
select a= '- ',b= ', ' union all
select a= '+ ',b= ', ' union all
select a= '/ ',b= ', ' union all
select a= '* ',b= ', '
) a

select @s2=replace(@s2,a,b)+ ', '
from
(
select a= '( ',b= '(, ' union all
select a= ') ',b= ',) ' union all
select a= '- ',b= ',-, ' union all
select a= '+ ',b= ',+, ' union all
select a= '/ ',b= ',/, ' union all
select a= '* ',b= ',*, '
) a
print @s2
select @s2=case when charindex(col2,@s2)> 0 then replace(@s2,col2, '
(select actdata from a where item_id= ' ' '+col2+ ' ' ') ') end from dbo.f_split(@sql, ', ')
select @s2= 'insert c select ' ' '+@s1+ ' ' ',(select ( '+replace(@s2, ', ', ' ')+ ')) '
exec(@s2)

while @@FETCH_STATUS=0
begin
fetch next from t_cursor into @s1,@s2
select @sql=@s2

select @sql=replace(@sql,a,b)
from
(select a= '( ',b= ' ' union all
select a= ') ',b= ' ' union all
select a= '- ',b= ', ' union all
select a= '+ ',b= ', ' union all
select a= '/ ',b= ', ' union all
select a= '* ',b= ', '
) a

select @s2=replace(@s2,a,b)+ ', '
from
(
select a= '( ',b= '(, ' union all
select a= ') ',b= ',) ' union all
select a= '- ',b= ',-, ' union all
select a= '+ ',b= ',+, ' union all
select a= '/ ',b= ',/, ' union all
select a= '* ',b= ',*, '
) a
print @s2
select @s2=case when charindex(col2,@s2)> 0 then replace(@s2,col2, '
(select actdata from a where item_id= ' ' '+col2+ ' ' ') ') end from dbo.f_split(@sql, ', ')
select @s2= 'insert c select ' ' '+@s1+ ' ' ',(select ( '+replace(@s2, ', ', ' ')+ ')) '
exec(@s2)
end
close t_cursor
deallocate t_cursor


/*---------显示结果------------------*/
select * from c


/*----------删除环境-----------------*/
drop table c
drop function f_split
drop table b
drop table a


/*------------结果-------------------*/
expressions_code expressions_end
---------------- ---------------
0101 .97
0102 -9.67
0103 180.47
0104 -93.20
0105 -5115.00
0201 168053.07

（所影响的行数为 6 行）