动态列计算问题，求高手看看
比如工资表
id sal hours days operator
1 200 2 3 +,*
2 250 3 4 *,-
3 350 2 5 -,/
operator 为列 sal 和hours，days 的运算顺序，
如id = 1， 为 (200+2）*3
id=2, 为 250*3-4
id=3, 为 （350-2）/5
如何用sql 计算每行的值？
谢谢！
[解决办法]
WITH a1 (id,sal,HOURS,DAYS,operator) AS
(
select 1, 200, 2, 3,'+,*' UNION ALL
select 2, 250, 3, 4,'*,-' UNION ALL
select 3, 350, 2, 5,'-,/'
)
,a2 AS
(
select
CASE
WHEN LEFT(operator,1)='+' THEN (sal+HOURS)
WHEN LEFT(operator,1)='-' THEN (sal-HOURS)
WHEN LEFT(operator,1)='*' THEN (sal*HOURS)
WHEN LEFT(operator,1)='/' THEN (sal/HOURS)
END result,DAYS,operator
FROM a1
)
select
CASE
WHEN right(operator,1)='+' THEN (result+DAYS)
WHEN right(operator,1)='-' THEN (result-DAYS)
WHEN right(operator,1)='*' THEN (result*DAYS)
WHEN right(operator,1)='/' THEN (result/DAYS)
END result
FROM a2
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [id] varchar(100), [sal] int, [hours] int, [days] int, [operator] varchar(100));
insert #temp
select '1','200','2','3','+,*' union all
select '2','250','3','4','*,-' union all
select '3','350','2','5','-,/'
--SQL:
SELECT
id, sal, [hours], [days], operator,
result = CAST(
CASE RIGHT(operator, 1)
WHEN '+' THEN (result+[days])
WHEN '-' THEN (result-[days])
WHEN '*' THEN (result*[days])
WHEN '/' THEN (1.*result/[days])
END AS FLOAT)
FROM
(
SELECT
id, sal, [hours], [days], operator,
result =
CASE LEFT(operator, 1)
WHEN '+' THEN (sal+[hours])
WHEN '-' THEN (sal-[hours])
WHEN '*' THEN (sal*[hours])
WHEN '/' THEN (1.*sal/[hours])
END
from #temp
) t
/*
idsalhoursdaysoperatorresult
120023+,*606
225034*,-746
335025-,/69.6
*/
create table 工资表
(id int, sal int, hours int, days int, operator varchar(5))
insert into 工资表
select 1, 200, 2, 3, '+,*' union all
select 2, 250, 3, 4, '*,-' union all
select 3, 350, 2, 5, '-,/'
-- 建结果表
create table #return(id int, gz int)
-- 计算工资
declare @id int,@s int,@h int,@d int,@op varchar(5),@tsql varchar(100)
declare ap scroll cursor for
select id,sal,hours,days,operator from 工资表
open ap
fetch first from ap into @id,@s,@h,@d,@op
while(@@fetch_status<>-1)
begin
select @tsql='insert into #return(id,gz) select '+rtrim(@id)+', '
+'('+rtrim(@s)+replace(@op,',',rtrim(@h)+N')')+rtrim(@d)
exec(@tsql)
fetch next from ap into @id,@s,@h,@d,@op
end
close ap
deallocate ap
-- 结果
select id,gz from #return
/*
id gz
----------- -----------
1 606
2 746
3 69
(3 row(s) affected)
*/