问具有多个随机值但和总是在一个范围内的MySQL查询

EN

+----+--------------------+-------+
| id |        item        | price |
+----+--------------------+-------+
|  1 | apple              | 10.5  |
|  2 | banana             | 1.85  |
|  3 | carrot             | 16.22 |
|  4 | donut              | 13.33 |
|  5 | eclair             | 18.85 |
|  6 | froyo              | 26.99 |
|  7 | gingerbread        | 12.15 |
|  8 | honecomb           | 50.68 |
|  9 | ice-cream-sandwich | 2.44  |
| 10 | jellybean          | 2.45  |
| 11 | kitkat             | 2.46  |
| 12 | lollipop           | 42.42 |
+----+--------------------+-------+

SELECT Count(`id`)        AS `item_count`, 
       Round(`price` / 5) `price_group` 
FROM   `items` 
WHERE  `price` <= 35 
GROUP  BY `price_group` 
ORDER  BY `price_group` ASC; 

+------------+-------------+
| item_count | price_group |
+------------+-------------+
|          4 |           0 |
|          2 |           2 |
|          2 |           3 |
|          1 |           4 |
+------------+-------------+

SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 0 ORDER BY rand() ASC LIMIT 2;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 4 ORDER BY rand() ASC LIMIT 1;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 3 ORDER BY rand() ASC LIMIT 1;
or
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 0 ORDER BY rand() ASC LIMIT 1;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 3 ORDER BY rand() ASC LIMIT 1;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 2 ORDER BY rand() ASC LIMIT 2;

<?php

function rand_weighted($weight, $total){
    return (float)mt_rand()*(float)$weight/((float)mt_getrandmax()*(float)$total);
};

//you can change these
$targetPrice = 35.00;
$numProducts = rand(3,6);
$maxVariance = 20.00;

$priceGroupIncrement = $maxVariance / $numProducts;
$targetPriceGroupSum = (int)round($targetPrice/$priceGroupIncrement, 0);

$select = "SELECT COUNT(`id`) AS `item_count`, ROUND(`price`/{$priceGroupIncrement}) `price_group`";
$from = "FROM `items`";
$where = "WHERE `price` <= {$targetPrice}";
$groupBy = "GROUP BY `price_group`";
$orderBy = "ORDER BY `price_group` ASC"; //for readability of result set, not necessary
$sql = "{$select} {$from} {$where} {$groupBy} {$orderBy}";

echo "SQL for price groups:\n{$sql};\n\n";

//run your query here and get the result set
//here is a sample result set
//this assumes $targetPrice = 35.00, $numProducts=4, and $maxVariance=20.00
$numProducts = 4;
$priceGroupIncrement = 5;
$targetPriceGroupSum = 7;
$resultSet = array(
    array('item_count'=>4, 'price_group'=>0),
    array('item_count'=>2, 'price_group'=>2),
    array('item_count'=>2, 'price_group'=>3),
    array('item_count'=>1, 'price_group'=>4),
);
//end sample result set

$priceGroupItemCount = array();
$priceGroupWeight = array();
$total = 0;

//randomly weight price group based on how many items are in the group
foreach ($resultSet as $result){
    $priceGroupItemCount[$result['price_group']] = $result['item_count'];
    $total += $result['item_count'];
}
foreach ($resultSet as $result){
    $priceGroupWeight[$result['price_group']] = rand_weighted($result['item_count'], $total);
}

//recursive anonymous function to find a match
$recurse = function($priceGroupWeight, $selection=array(), $priceGroupSum=0) use ($priceGroupItemCount, $total, $numProducts, $targetPriceGroupSum, &$recurse){
    //sort by random weighted value
    arsort($priceGroupWeight);
    //iterate through each item in the $priceGroupWeight associative array
    foreach ($priceGroupWeight as $priceGroup => $weight){
        //copy variables so we can try a price group
        $priceGroupWeightCopy = $priceGroupWeight;
        $selectionCopy = $selection;
        $priceGroupSumCopy = $priceGroupSum + $priceGroup;
        //try to find a combination that adds up to the target price group
        if (isset($selectionCopy[$priceGroup])){
            $selectionCopy[$priceGroup]++;
        } else {
            $selectionCopy[$priceGroup] = 1;
        }
        $selectionCount = array_sum($selectionCopy);
        if ($priceGroupSumCopy == $targetPriceGroupSum && $selectionCount == $numProducts) {
            //we found a working solution!
            return $selectionCopy;
        } else if ($priceGroupSumCopy < $targetPriceGroupSum && $selectionCount < $numProducts) {
            //remove the item from the price group
            unset($priceGroupWeightCopy[$priceGroup]);
            //if there is still remaining items in the group, add the adjusted weight back into the price group
            $remainingInPriceGroup = $priceGroupItemCount[$priceGroup] - $selectionCopy[$priceGroup];
            if ($remainingInPriceGroup > 0){
                $remainingTotal = $total - count($selection);
                $priceGroupWeightCopy[$priceGroup] = rand_weighted($remainingInPriceGroup, $remainingTotal);
            }
            //try to find the solution by recursing
            $tryRecursion = $recurse($priceGroupWeightCopy, $selectionCopy, $priceGroupSumCopy);
            if ($tryRecursion !== null){
                return $tryRecursion;
            }
        }
    }
    return null;
};

$selection = $recurse($priceGroupWeight);

if ($selection===null){
    echo "there are no possible solutions\n";
} else {
    echo "SQL for items:\n";
    foreach ($selection as $priceGroup => $numberFromPriceGroup){
        $select = "SELECT `items`.*";
        $from = "FROM `items`";
        $where = "WHERE ROUND(`price`/{$priceGroupIncrement}) = {$priceGroup}";
        $orderBy = "ORDER BY rand() ASC";
        $limit = "LIMIT {$numberFromPriceGroup}";
        $sql = "{$select} {$from} {$where} {$orderBy} {$limit}";
        echo "$sql;\n";
    }
}

SELECT item, price, sum(price) as total_price
FROM items 
GROUP BY item
HAVING total_price BETWEEN ($value-10) AND ($value+10) 
ORDER BY rand() LIMIT 6