SQl-根据数量选择最佳价格

Question

我有一个表格，其中包含根据订购的数量和下订单的客户类型的特定项目的价格...

ID    Name     Quantity     ClientType    Price/Unit ($)
========================================================
1     Cheese      10        Consumer      20
2     Cheese      20        Consumer      15
3     Cheese      30        Consumer      12
4     Cheese      10        Restaurant    18
5     Cheese      20        Restaurant    13
6     Cheese      30        Restaurant    10

我在SQL中的WHERE子句中遇到问题，无法选择客户根据订购数量获得最佳价格的行。规则是，他们必须至少满足数量，才能获得该定价层的价格。如果他们的订单低于最低数量，那么他们将获得第一个数量的价格（本例中为10），如果他们的订单超过最大数量（本例中为30），则他们将获得该价格。

例如如果一家餐厅订购26单位奶酪，则应选择ID=5的那一行。如果消费者订购了9个单位的奶酪，则返回的行ID应为1。如果消费者订购50单位的奶酪，那么他们应该得到ID=3。

declare @SelectedQuantity INT;

SELECT * 
FROM PriceGuide
WHERE Name = 'Cheese'
  AND ClientType = 'Consumer'
  AND Quantity <= @SelectedQuantity

WHERE子句中缺少什么？

Answer 1

您的查询将返回小于@SelectedQuantity的所有匹配行。您只想返回最大数量小于所选数量的行，因此需要子查询来获得此结果：

SELECT * 
FROM PriceGuide a
WHERE a.Name = 'Cheese'
  AND a.ClientType = 'Consumer'
  AND a.Quantity = (SELECT MAX(Quantity) FROM PriceGuide
  WHERE ClientType = a.ClientType
  AND Name = a.Name
  AND Quantity <= @SelectedQuantity)

Answer 2

这有点棘手，因为您需要处理小于10的数量。

我认为最好的办法是：

SELECT TOP 1 *
FROM PriceGuide
WHERE Name = 'Cheese' AND ClientType = 'Consumer'
ORDER BY (CASE WHEN @SelectedQuantity >= Quantity THEN 1 ELSE 0 END) DESC, 
         (CASE WHEN @SelectedQuantity >= Quantity THEN PriceUnit END) ASC,
         Quantity ASC;

此版本通过保留给定Name/ClientType的所有行来处理最小数量，使用ORDER BY进行优先级排序。

Answer 3

编辑

如评论中所述，第一个解决方案没有正确处理特殊情况。

下一个尝试：

SELECT TOP 1 ID, Name, Quantity, ClientType, [Price/Unit]
FROM PriceGuide
WHERE Name = 'Cheese'
  AND ClientType = 'Consumer'
ORDER BY CASE WHEN Quantity <= @SelectedQuantity THEN Quantity ELSE -Quantity END DESC

假设Quantity为正，orderby将返回满足数量的行

对于与此条件不匹配的行，它使用-Quantity进行排序。因此，如果没有符合条件的行，将返回数量最小的行。