34,594
社区成员
发帖
与我相关
我的任务
分享
create table [tb]([合同号] varchar(7),[费用名称] varchar(6),[费用] int,[币种] varchar(3))
insert [tb]
select '10T0001','佣金',500,'USD' union all
select '10T0001','检验费',200,'USD' union all
select '10T0001','海运费',300,'USD' union all
select '10T0001','内运费',1500,'RMB' union all
select '10T0002','佣金',600,'USD' union all
select '10T0002','检验费',400,'USD' union all
select '10T0002','海运费',600,'USD' union all
select '10T0002','内运费',2000,'RMB'
select 费用=sum(费用*(case when 币种='USD' then 6.82 else 1 end)) from tb where 合同号='10T0001'
/**
费用
----------------------------------------
8320.00
(所影响的行数为 1 行)
**/
当然没有的话
可以这样:
select
sum(case 币种 when 'USD' then 费用*6.82 when 'EUR' then 费用*9.82 when 'RMB' then 费用*1.0 end) as 费用
from
tb
where
....
if not object_id('tb') is null
drop table tb
Go
Create table tb([合同号] nvarchar(7),[费用名称] nvarchar(3),[费用] int,[币种] nvarchar(3))
Insert tb
select N'10T0001',N'佣金',500,N'USD' union all
select N'10T0001',N'检验费',200,N'USD' union all
select N'10T0001',N'海运费',300,N'USD' union all
select N'10T0001',N'内运费',1500,N'RMB' union all
select N'10T0002',N'佣金',600,N'USD' union all
select N'10T0002',N'检验费',400,N'USD' union all
select N'10T0002',N'海运费',600,N'USD' union all
select N'10T0002',N'内运费',2000,N'RMB'
Go
select sum(case when 币种='USD' then 费用*6.82 else 0 end)+
sum(case when 币种='EUR' then 费用*9.82 else 0 end)+
sum(case when 币种='RMB' then 费用 else 0 end)
from tb
where 合同号='10T0001'
---------------------------------------
8320.00
(1 行受影响)
select sum(case when 币种='USD' then 费用*6.82 else 0 end)+
sum(case when 币种='EUR' then 费用*9.82 else 0 end)+
sum(case when 币种='RMB' then 费用 else 0 end)
if not object_id('tb') is null
drop table tb
Go
Create table tb([合同号] nvarchar(7),[费用名称] nvarchar(3),[费用] int,[币种] nvarchar(3))
Insert tb
select N'10T0001',N'佣金',500,N'USD' union all
select N'10T0001',N'检验费',200,N'USD' union all
select N'10T0001',N'海运费',300,N'USD' union all
select N'10T0001',N'内运费',1500,N'RMB' union all
select N'10T0002',N'佣金',600,N'USD' union all
select N'10T0002',N'检验费',400,N'USD' union all
select N'10T0002',N'海运费',600,N'USD' union all
select N'10T0002',N'内运费',2000,N'RMB'
Go
select 费用=sum(case when 币种='USD' then 费用*6.82
when 币种='EUR' then 费用*9.82
when 币种='RMB' then 费用*1.0
end)
from tb where 合同号='10T0001'
/*
费用
---------------------------------------
8320.00
(1 個資料列受到影響)
*/