declare @Number int
set @Number = 100
select d.ID,
d.MerchantID,
d.State,
d.EffectiveDate,
d.ChargesType,
d.ChargesName,
d.ChargesCode,
d.InvoiceCol,
d.ValueType,
d.Value,
@Number as NarmalSelling,
Tax= Case
WHEN c.ValueType = 'Rate' THEN @Number*abs(cast(c.Value as decimal(18,2)))
WHEN c.ValueType = 'Number' THEN @Number-abs(cast(c.Value as decimal(18,2)))
ELSE 0 END
from Config_Order_Charges d
left join
(select *
from Config_Order_Charges
where ID in(
select MAX(ID) as ID
from Config_Order_Charges a
inner join
(select ChargesType,
MAX(EffectiveDate) EffectiveDate
from Config_Order_Charges
group by ChargesType,MerchantID
having MerchantID = 'WER') b
on a.ChargesType=b.ChargesType and a.EffectiveDate=b.EffectiveDate
where a.MerchantID = 'WER'
group by a.ChargesType)) c
on c.ID = d.ID
where d.MerchantID = 'WER'
order by d.ChargesType,d.EffectiveDate desc,d.ID desc
----------------------
结果显示