有人可以帮助调试SQL查询吗？ [英] Can someone help in debugging SQL query?
问题描述
我遇到一些疑问问题
以下是我想要的例子
I am having some issue with some query
Here is example of what I want
@QuantityEntered = 1返回1.50，因为1介于1和5之间，1.50是从价格中添加的字段总计1.50
@QuantityEntered = 2返回3.00因为2落在1和5之间，1.50从价格字段中添加总计3.00
@QuantityEntered = 3返回4.50因为3落在1和5之间，1.50来自价格字段，总计增加4.50
@QuantityEntered = 4返回6.00因为4落在1到5之间，1.50来自价格字段添加总计6.00
@QuantityEntered = 5返回7.50因为5落在1和5之间，1.50来自价格字段，总计7.50
@QuantityEntered = 6返回8.75，因为6介于6和10之间，1.25来自价格区域添加总计8.75
@QuantityEntered = 7返回10.00因为7落在6到10之间，1.25来自价格字段，总计10.00
@QuantityEntered = 8返回11.25因为8落在6到10之间，而1.25是从总价值11.25增加的价格字段
@QuantityEntered = 9返回12.50因为9落在6到10之间，1.25来自价格字段总计12.50
@QuantityEntered = 10返回13.75因为10落在6到10之间，1.25来自价格字段，总计13.75
@QuantityEntered = 11返回14.75因为11落在11和999999999之间，1.00来自价格字段，总计14.75
@QuantityEntered = 12返回15.75因为12落在12和999999999之间，1.00来自价格字段总共15.75
@QuantityEntered = 1 returns 1.50 because 1 falls in between 1 and 5 and 1.50 is added from price field for a total of 1.50
@QuantityEntered = 2 returns 3.00 because 2 falls in between 1 and 5 and 1.50 is added from price field for total of 3.00
@QuantityEntered = 3 returns 4.50 because 3 falls in between 1 and 5 and 1.50 is from price field added for total of 4.50
@QuantityEntered = 4 returns 6.00 because 4 falls in between 1 and 5 and 1.50 is from price field added for total of 6.00
@QuantityEntered = 5 returns 7.50 because 5 falls in between 1 and 5 and 1.50 is from price field added for total of 7.50
@QuantityEntered = 6 returns 8.75 because 6 falls in between 6 and 10 and 1.25 is from price field added for total of 8.75
@QuantityEntered = 7 returns 10.00 because 7 falls in between 6 and 10 and 1.25 is from price field added for total of 10.00
@QuantityEntered = 8 returns 11.25 because 8 falls in between 6 and 10 and 1.25 is from price field added for total of 11.25
@QuantityEntered = 9 returns 12.50 because 9 falls in between 6 and 10 and 1.25 is from price field added for total of 12.50
@QuantityEntered = 10 returns 13.75 because 10 falls in between 6 and 10 and 1.25 is from price field added for total of 13.75
@QuantityEntered = 11 returns 14.75 because 11 falls in between 11 and 999999999 and 1.00 is from price field added for total of 14.75
@QuantityEntered = 12 returns 15.75 because 12 falls in between 12 and 999999999 and 1.00 is from price field added for total of 15.75
但是我得到了这个
but i am getting this
@QuantityEntered = 1返回1.50
@QuantityEntered = 2返回3.00
@QuantityEntered = 3返回4.50
@QuantityEntered = 4返回6.00
@QuantityEntered = 5返回7.50
@QuantityEntered = 6返回8.75
@QuantityEntered = 7返回10.00
@QuantityEntered = 8返回11.25
@QuantityEntered = 9返回12.50
@QuantityEntered = 10返回20
@QuantityEntered = 1 returns 1.50
@QuantityEntered = 2 returns 3.00
@QuantityEntered = 3 returns 4.50
@QuantityEntered = 4 returns 6.00
@QuantityEntered = 5 returns 7.50
@QuantityEntered = 6 returns 8.75
@QuantityEntered = 7 returns 10.00
@QuantityEntered = 8 returns 11.25
@QuantityEntered = 9 returns 12.50
@QuantityEntered = 10 returns 20
我的尝试：
What I have tried:
DECLARE @QuantityEntered int
DECLARE @ClassEntered int
SET @QuantityEntered = 1;
SET @ClassEntered = 1
--SELECT
-- Sum(IIf(@QuantityEntered>[MaxValue],[MaxValue]*[Price],(@QuantityEntered-([MinValue]-1))*[Price])) AS RangePrice
--FROM
-- TierPricing
--WHERE
-- (((TierPricing.MinValue)<=@QuantityEntered) AND ((TierPricing.Class)=@ClassEntered));
--Select * from TierPricing
--WHERE
-- (((TierPricing.MinValue)<=@QuantityEntered) AND ((TierPricing.Class)=@ClassEntered));
SELECT
Sum(IIf(@QuantityEntered>=TierPricing.MaxValue,TierPricing.MaxValue*TierPricing.Price,(@QuantityEntered-(TierPricing.MinValue-1))*TierPricing.Price)) AS RangePrice
FROM
TierPricing
WHERE
(((TierPricing.MinValue)<=@QuantityEntered) AND ((TierPricing.Class)=@ClassEntered));
推荐答案
你可以用另一种方式重新解决问题。
You can reformulate the problem another way.
RangePrice = @QuantityEntered + IIF(@QuantityEntered <= 10 , @QuantityEntered, 10) * 0.25 + IIF(@QuantityEntered <= 5, @QuantityEntered, 5)* 0.25
这篇关于有人可以帮助调试SQL查询吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
