--生成数据
create table #t(id int,name varchar(10),renkou int,mianji numeric(10,1),gongshi varchar(50))
insert into #t select 1,'张三',4,100.0,'[renkou]*10*0.4+([mianji]-[renkou]*10)*1.6'
insert into #t select 2,'李四',3,85.0 ,'[renkou]*10*0.4+([mianji]-[renkou]*10)*1.6'
insert into #t select 3,'王五',6,120.0,'[mianji]*0.4'--执行查询
declare @s varchar(8000)
set @s = 'select *,result = case gongshi '
select @s = @s + ' when '''+gongshi+''' then '+gongshi from #t group by gongshi
exec(@s + ' end from #t')
--结果
id name renkou mianji gongshi result
-------------------------------------------------------------------------------------
1 张三 4 100.0 [renkou]*10*0.4+([mianji]-[renkou]*10)*1.6 112.00
2 李四 3 85.0 [renkou]*10*0.4+([mianji]-[renkou]*10)*1.6 100.00
3 王五 6 120.0 [mianji]*0.4 48.00
create table #t(id int,name varchar(10),renkou int,mianji numeric(10,1),gongshi varchar(50))
insert into #t select 1,'张三',4,100.0,'[renkou]*10*0.4+([mianji]-[renkou]*10)*1.6'
insert into #t select 2,'李四',3,85.0 ,'[renkou]*10*0.4+([mianji]-[renkou]*10)*1.6'
insert into #t select 3,'王五',6,120.0,'[mianji]*0.4'--执行查询
declare @s varchar(8000)
set @s = 'select *,result = case gongshi '
select @s = @s + ' when '''+gongshi+''' then '+gongshi from #t group by gongshi
exec(@s + ' end from #t')
--结果
id name renkou mianji gongshi result
-------------------------------------------------------------------------------------
1 张三 4 100.0 [renkou]*10*0.4+([mianji]-[renkou]*10)*1.6 112.00
2 李四 3 85.0 [renkou]*10*0.4+([mianji]-[renkou]*10)*1.6 100.00
3 王五 6 120.0 [mianji]*0.4 48.00
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货