MySQL 运行总计

Question

我有一个存储金额和利率的表，如下所示。这些实际上是我正在构建的一个简单系统上的贷款出价或出价。如果有一笔 1000000 英镑的贷款，则该贷款可以有多个出价/出价。

我需要按照最佳利率对这些出价进行排序，这是我可以做到的。但是，我无法保留运行总计来跟踪哪些出价可以接受。

这是我的 table 。

+-----+--------+---------------+------------+
| id  | amount | interest_rate | aggregated |
+-----+--------+---------------+------------+
| 105 | 100000 | 5             |     100000 |
| 108 | 500000 | 6.75          |     600000 |
| 107 |  50000 | 7             |     650000 |
| 106 | 100000 | 8             |     750000 |
| 112 | 500000 | 8.75          |    1250000 |
| 111 |   5000 | 16            |    1255000 |
| 110 | 500000 | 20            |    1755000 |
+-----+--------+---------------+------------+

在这里你可以看到我已经成功获得了一个聚合列。但是，我需要获取低于 1000000 英镑的所有出价，这意味着如果我使用聚合列，它只会返回前四名。

为了让您更好地了解，这里再次列出了我需要的所需返回结果。

+-----+--------+---------------+------------+--------+--------+
| id  | amount | interest_rate | aggregated | wanted | total  |
+-----+--------+---------------+------------+--------+--------+
| 105 | 100000 | 5             |     100000 | *      | 100000 |
| 108 | 500000 | 6.75          |     600000 | *      | 600000 |
| 107 |  50000 | 7             |     650000 | *      | 650000 |
| 106 | 100000 | 8             |     750000 | *      | 750000 |
| 112 | 500000 | 8.75          |    1250000 |        | 750000 |
| 111 |   5000 | 16            |    1255000 | *      | 755000 |
| 110 | 500000 | 20            |    1755000 |        | 755000 |
+-----+--------+---------------+------------+--------+--------+

基本上，我想选择按interest_rate排序且小于1000000的所有行。您可以在此处看到我们跳过了第112行，因为750000 + 1250000 > 1000000，因此我们跳过它并继续。

这是我当前用于返回这些结果的简单 SQL。

SET @aggregated = 0;
SET @position = 0;
SELECT id, amount, interest_rate, aggregated FROM (
    SELECT 
        *,
        @aggregated := @aggregated + `auction_bids`.`amount` as `aggregated`,
        @position := @position + 1 as `position`        
    FROM 
        `auction_bids` 
    WHERE 
        `auction_bids`.`auction_id` = 21 AND
        `auction_bids`.`amount` < 1000000
    ORDER BY 
        `auction_bids`.`interest_rate` ASC
) as `a`;

Answer 1

这可能会有所帮助，尽管当我尝试在 SQLFiddle 上的嵌套查询中使用它时会出错:

set @aggregated := 0;
set @position := 0;
set @inc := 0;

select
    *,
    @aggregated := @aggregated + `auction_bids`.`amount` as `aggregated`,
    @position := @position + 1 as `position`,
    case 
        when @inc + `auction_bids`.`amount` <= 1000000 then 1
        else 0 
    end as `wanted`,
    @inc := @inc + case 
        when @inc + `auction_bids`.`amount` > 1000000 then 0 
        else `auction_bids`.`amount` 
    end as `includedaggregate`
from
    `auction_bids` 
where 
    `auction_bids`.`auction_id` = 21 and
    `auction_bids`.`amount` < 1000000
order by
    `auction_bids`.`interest_rate`

Example SQLFiddle