Запрос, учитывающий различные комбинации

Прежде всего, мои извинения за заголовок этого вопроса, на данный момент у меня нет лучшей идеи. Сделайте хорошее предложение, и я исправит заголовок. (Если у меня есть разрешение на это, я на самом деле не знаю.)

Ситуация:

Мне сложно выполнить правильный 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:
Пользователь размещает заказ:

• 5 раз продукт1,
• 5 раз продукт2,
• 5 умножить на product3,
• 3 умножить на product4,
• 3 умножить на product5.

В целом на PriceA покупатель получает 3-кратную скидку, так как имеется 3 полных набора по 5 штук

Пример 2: Пользователь размещает заказ:

• 5 раз продукт1,
• 5 раз продукт2,
• 5 раз продукт3,
• 5 раз продукт4,
• 2 раза продукт5,
• 2 раза продукт6,
• 2 раза product7

Все по ценеA. Теперь покупатель получает 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 элементов, а затем проигнорирует the

Вопрос в том, разрешимо ли это в SQL? Или мне лучше было бы расширить свой выбор и провести математические вычисления с помощью скриптов? (Мое веб-приложение написано на PHP, поэтому у меня есть место для некоторой серверной математики.)

Решение:

Я реализовал решение Нила; теперь он выглядит так:

/** @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);
        } 
    } 
}