create table #t1(类型 varchar(8),折扣价 varchar(100),积分 varchar(100))
insert #t1 select
'一般会员','本次消费额*0.95','积分+round(本次消费额/1000,0)'
Union all select
'白银会员','本次消费额*0.9','积分+round(本次消费额/1000,0)'
Union all select
'黄金会员','本次消费额*0.85','积分+round(本次消费额/1000,0)'
Union all select
'钻石会员','本次消费额*0.8','积分+round(本次消费额/1000,0)'
create table #t2(会员编号 char(3),类型 varchar(8),本次消费额 numeric(10,2),折扣价 numeric(10,2),积分 int)
insert #t2 select
'002','白银会员',1520.00, 0, 0
union all select
'005','黄金会员',3300.00, 0, 0
union all select
'007','一般会员',350.00, 0 , 0
union all select
'034','钻石会员',520.00, 0 , 0
union all select
'002','白银会员',780.00, 0 , 0
update a
set a.折扣价=a.本次消费额*b.moneys,a.积分=a.积分+round(a.本次消费额/1000,0)
from #t2 a
join (select 类型, convert(smallmoney,substring(折扣价,patindex('%[0-9]%',折扣价),len(折扣价))) moneys
from #t1) b
on a.类型=b.类型