过滤重复值和取最近的时间-优快云博客

本文提供了一个SQL查询案例，展示了如何从Config_Order_Charges表中选择特定字段，并通过LEFT JOIN结合子查询来获取最新的订单费用配置。此外，还演示了如何根据Value类型计算税费。

        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
       ----------------------
        结果显示