Как написать запрос mysql на выборку из таблицы строк на сумму не выше указанной?

Question

[H2Otik]

Здравствуйте, помогите составить MysQL запрос
Есть таблица

id    price(цена)   effect(эффективность)
21	  10	              56
23	  70	              20
24	  20	              45
25	  30	              0
26	  50	              30

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` int(11) NOT NULL,
  `effect` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table` (`id`, `price`, `effect`)
VALUES
	(1,10,56),
	(2,50,65),
	(3,35,20),
	(4,70,50),
	(5,10,0);

Нужно выбрать из таблицы строки с максимальной эффективностью на сумму не выше указанной (например, 100).

Answer 1

[Fedot01]

SELECT *, @sum := @sum + price FROM (SELECT *, @sum := 0 FROM `table` WHERE price <= 100 ORDER BY `effect` DESC) r WHERE  (@sum + price) <= 100

Answer 2

[Алексей Кулешов]

SELECT * FROM `table` ORDER BY `effect` DESC

...
$total = 0;
while ($row = $result->fetch_assoc()) {
  $tmp = $total + $row['price'];
  if($tmp < 100){
    $total += $row['price'];
  } else break;
}
...

Answer 3

[Илья Лесных]

Можно попробовать использовать having sum(price) < 100 с сортировкой по эффективности, но вряд ли. Проще просто вернуть все данные, отсортировав по возрастанию и дальше в php написать функцию примерно такого вида:

function getRows($rows, $limit = 100) {
    $_effectCount = 0;
    $result = array();
    foreach ($rows as $row) {
        $_effectCount += $row['effect'];
        if ($_effectCount >= $limit) {
            break;
        }
        $result[] = $row;
    }
    return $result;
}

Comments

[Алексей Кулешов]

having sum(price) < 100 - в задаче указано "с максимальной эффективностью" - следовательно расчеты необходимо проводить в определённом порядке - одним SQL-запросом не обойтись. Можно конечно поиграться с курсорами, или написать хранимую процедуру - но для удобства понимания логики системы расчета - лучше реализовать средствами языка программирования.

[H2Otik]

В таблице может быть большое количество данных, потому возвращать все данные и потом перебирать это в цикле, очень не эффективно

[Алексей Кулешов]

Загружать SQL-сервер возможно рекурсивным запросом - совсем не логично.

Answer 4

[Yurii Medvedev]

SELECT t.*,
       @sum := @sum + effect
FROM (
	SELECT *
	FROM `table`
	ORDER BY effect DESC
      ) AS t
      JOIN (SELECT @sum := 0) var
WHERE @sum < 100;