sql - 计算不同组合的查询

Question

首先，对于这个问题的标题，我深表歉意，目前我没有更好的主意。提出一个好的建议，我会修改标题。 (如果我有权这样做，我实际上不知道。)

情况:

我很难完成正确的 SQL 查询。我有一个设置，人们可以在其中使用产品等下订单，并且在某些情况下他们可以获得折扣。

考虑以下架构:

Product:
  [...]

Price:
  product_id: integer
  description: string
  [...]

Order:
  [...]

OrderItem:
  order_id: integer
  price_id: integer
  amount: integer

并考虑以下规则:

• 有 9 种不同的产品。
• 所有产品都有 2 个价格，一个描述为 PriceA，一个描述为 PriceB。
• 所有这些价格对于每种产品的每种类型都是相同的。 (也就是说，所有 PriceA 价格都相同，所有 PriceB 价格也相同。)

问题:

对于价格水平相同的每组 5 个不同产品(即 PriceA 与 PriceB)，订单的总价格会降低一定数量。我正在尝试编写一个查询，告诉我发生了多少次。

例子:

示例 1:
用户下订单:

• product1 的 5 倍，
• product2 的 5 倍，
• product3 的 5 倍，
• 3 倍 product4,
• 产品的 3 倍5。

全部为 PriceA，客户获得 3 倍的折扣，因为有 3 套完整的 5

示例 2:用户下订单:

• 产品 1 的 5 倍，
• 产品 2 的 5 倍，
• 5 倍 product3,
• 5 倍 product4,
• 2 乘积5，
• 2 乘积6，
• 2倍乘积7

所有PriceA价格。现在，客户收到 5 倍的折扣，因为有 4 套 5，两套涉及 product5，两套涉及 product6，一套涉及 product7。

斗争:

我试过这个SQL:

SELECT min(amount) as amount from
    (SELECT oi.amount from `order` o
        inner join orderitem oi on oi.order_id = o.id
        inner join price p on oi.price_id = p.id AND p.description = "PriceA"
        inner join product pr on p.product_id = pr.id
        order by oi.amount desc
        limit 5) as z
    having count(amount) = 5;

这对于示例 1 非常有效，但在示例 2 中，它会给出错误的结果，因为它会选择第一组 5 个项目，然后忽略

问题是:这在 SQL 中可以解决吗？或者我会更好地扩大我的选择范围并通过脚本进行计算吗？ (我的 Web 应用程序是用 PHP 编写的，所以我确实有一些服务器端数学的空间。)

解决方案:

我实现了 Neil 的解决方案；现在看起来像这样:

/** @var $oid integer The order ID. */

/* Select all amounts ordered per item, only for a given price title. */
$sql = <<<SQL
SELECT oi.amount as amount FROM orderitems oi
    INNER JOIN orders   o  ON oi.order_id  = o.id AND o.id = $oid
    INNER JOIN prices   p  ON oi.price_id  = p.id AND p.title = '%s'
    INNER JOIN products pr ON p.product_id = pr.id
    ORDER BY oi.amount DESC
SQL;
$discountInfo = array(
    array(
        'price'     => 'PriceA',
        'discounts' => array(
            9 => 49.50, /* Key is 'size of set', value is 'discount'. */
            5 => 23.50
        ),
    ),
    array(
        'price' => 'PriceB',
        'discounts'  => array(
            9 => 22,
            5 => 10,
        ),
    ),
);

foreach($discountInfo as $info)
{
    /* Store all ordered amounts per item in Array. */
    $arr = array();
    $result = $this->dbQuery(sprintf($sql,$info['price']));
    while ($row = mysql_fetch_assoc($result)) $arr[] = $row['amount'];

    foreach ($info['discounts'] as $am => $di)
    {
        /* For each highest set of $am items, fetch the smallest member. */
        while (count($arr) >= $am)
        {
            /* Add discount for each complete set */
            $discount += $di * $arr[$am - 1];

            /* Substract the amounts from all members of the set */
            for ($i=0; $i<=$am - 1; $i++) $arr[$i] -= $arr[$am - 1];

            /* Remove all elements that are 0 */
            foreach ($arr as $k=>$v) if ($v == 0) unset ($arr[$k]);

            /* Array is messed up now, re-sort and re-index it. */
            rsort($arr);
        } 
    } 
}

Answer 1

这就是我在代码中的做法:将商品分成两个数组，每个数组对应一个价格水平。对于每个产品数组，数组中至少有五个产品:

1. 将数组按产品项数降序排列
2. 将数组中第五个产品的项目数添加到折扣总数中
3. 从数组中的前五个产品中减去数组中第五个产品的项目数
4. 从数组中删除所有零元素