34,590
社区成员
发帖
与我相关
我的任务
分享
declare @table table (ID int,CName varchar(6),Account int,Note varchar(3))
insert into @table
select 1,'海运费',200,'USD' union all
select 1,'服务费',300,null union all
select 1,'税款',50,null union all
select 2,'海运费',300,'USD' union all
select 2,'服务费',400,null union all
select 3,'服务费',500,null union all
select 3,'税款',100,null
select ID,
sum(Account*CASE WHEN Note IS NULL THEN 1 ELSE 7 end) AS 费用合计
from @table GROUP BY ID
/*
ID 费用合计
----------- -----------
1 1750
2 2500
3 600
*/
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:表a
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'表a')
AND type in (N'U'))
DROP TABLE 表a
GO
---->建表
create table 表a([ID] int,[CName] varchar(6),[Account] int,[Note] varchar(3))
insert 表a
select 1,'海运费',200,'USD' union all
select 1,'服务费',300,null union all
select 1,'税款',50,null union all
select 2,'海运费',300,'USD' union all
select 2,'服务费',400,null union all
select 3,'服务费',500,null union all
select 3,'税款',100,null
GO
--> 查询结果
SELECT ID,SUM(case when Note ='USD' then Account* 7 else Account end ) FROM 表a group by ID
--> 删除表格
--DROP TABLE 表a