--建立测试环境
Create Table 表(Name varchar(5),
Code varchar(2),
MaxPrice float,
ExPercent float,--附加率
Flag int --是否附加
)
--插入数据
insert into 表
select 'Ari','A',10000,0.5,1 union
select 'Ari','B',12000,0.4,1 union
select 'Ari','C',2000,0.2,1 union
select 'Ari','D',2000,0.2,0 union
select 'AD','A',10000,0.5,1 union
select 'AD','E',8000,0.4,1 union
select 'AD','F',4000,0.2,0 union
select 'AD','G',3000,0.2,1
--测试语句
select name,sum(Maxprice*mPercent) as Price from(
select name,Maxprice,case when
exists(select 1 from 表 where MaxPrice>a.MaxPrice and name=a.name) then
ExPercent* Flag
else 1 end as mPercent
from 表 a)a group by name
--删除测试环境
Drop Table 表
/*--
name Price
----- ----------
AD 13800.0
Ari 17400.0
--*/