Sql查询问题 - 使用案例为订购商品提供折扣 [英] Sql query question - give discounts to order items using case

问题描述

如何修改下面的查询为该订单项提供2％的折扣如果订单包含给定产品的五个或更多单位。 AND 为该行提供5％的折扣item如果订单包含10个或更多给定产品的单位。



出于某种原因，它只为我提供了10个或更多项目的输出。我需要在输出中显示两个折扣。谢谢。

How Can I modify the query below to give 2% discount for that line item If an order contains five or more units of a given product. AND give 5% discount for that line item If an order contains 10 or more units of a given product.

For some reason it only gives me an output for the the items that 10 or more. I need to show both discounts in the output. Thank you.

SELECT ORDERS.orderid, 
INVENTORY.partid, 
Inventory.description, 
ORDERITEMS.qty,
 Inventory.price,
 (OrderItems.qty * Inventory.price) as 'Total Original Price',

--If an order contains five or more units of a given product,  give a 2% discount for that line item. 
 (OrderItems.qty * Inventory.price * 
 CASE WHEN OrderItems.qty >= 5 THEN 0.05 ELSE 0.1 END) as 'Total Discounted Value',
 
--If an order contains ten or more units of a given product,   give a 5% discount on that line item.
(OrderItems.qty * Inventory.price * 
 CASE WHEN qty >=10 THEN 0.95/0.9 END) as 'Total Final Price of Each Product After Discount' 
 
 FROM orders, inventory, orderitems
 WHERE ORDERS.ORDERID = ORDERITEMS.ORDERID  AND ORDERITEMS.PARTID = INVENTORY.PARTID
 AND ORDERITEMS.qty >=5 AND ORDERITEMS.qty >=10

 ORDER BY ORDERS.orderid, INVENTORY.partid

推荐答案

1。查看你的where子句

1. Look on your where clause

AND ORDERITEMS.qty >=5 AND ORDERITEMS.qty >=10

这相当于ORDERITEMS.qty> = 10



2.看看你的情况

This equals to just ORDERITEMS.qty >=10

2. Look on your case

CASE WHEN OrderItems.qty >= 5

您需要限制间隔时间，如果您想要案例数量> = 10开始行动

you need to limit interval from right, if you want case qty>=10 to get into action

这篇关于Sql查询问题 - 使用案例为订购商品提供折扣的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！