很麻烦,还是到客户端做比较好create table test_u ( a varchar(4), b int)
goinsert test_u values ('USD',20)
insert test_u values ('RMB',10)
insert test_u values ('EUR',30)goselect a.a a ,b.a a1,convert(numeric(6,3),a.b*1.0/b.b )as b into #tmp from test_u a,test_u bselect * from #tmp变形的工作可以到客户端做
goinsert test_u values ('USD',20)
insert test_u values ('RMB',10)
insert test_u values ('EUR',30)goselect a.a a ,b.a a1,convert(numeric(6,3),a.b*1.0/b.b )as b into #tmp from test_u a,test_u bselect * from #tmp变形的工作可以到客户端做
insert into #t select 'USD' , 20
insert into #t select 'RMB' , 10
insert into #t select 'EUR' , 30
insert into #t select 'JAP' , 1000
declare @str varchar(5000)select @str= 'select a, '
select @str=@str+'cast( b as decimal(18,2))/'+ cast(b as varchar(10)) + ' '+ a+ ',' from #t
select @str= left(@str,len(@str)-1) + ' from #t'--print @str
exec(@str)
drop table #t
insert into #t select 'RMB' , 10
insert into #t select 'EUR' , 30
insert into #t select 'JAP' , 1000
insert into #t select 'FRC' , 40declare @str varchar(5000)select @str= 'select a, '
select @str=@str+'cast(1.0*b/'+ cast(b as varchar(10)) + ' as decimal(18,2)) '+ a+ ',' from #t
select @str= left(@str,len(@str)-1) + ' from #t'print @str
exec(@str)
drop table #t
select a_field,
sum(case a_field
when 'EUR' then b_field
when 'RMB' then b_field
else b_field end)/ (select b_field from us where a_field='USD' ) usd ,
sum(case a_field
when 'EUR' then b_field
when 'USD' then b_field
else b_field end)/ (select b_field from us where a_field='RMB' ) rmb ,
sum(case a_field
when 'USD' then b_field
when 'RMB' then b_field
else b_field end)/ (select b_field from us where a_field='EUR' ) eur
from us
group by a_field