我有一张商店商品的价格表。我正在尝试编写一个mysql查询，它在RANDOM中提取许多项目（在3到6之间），所有项目的TOTAL值都在用户选择的值的20美元以内。
有关于如何做到这一点的想法吗？提前感谢！
编辑***到目前为止，这就是我所拥有的。最大的问题是sum（price）取所有项目的总和。第二个问题是"LIMIT"是随机的，但我最终可以让php在运行查询之前选择一个介于3和6之间的随机数。
SELECT item,price,sum(price)
FROM items
WHERE sum(price) BETWEEN ($value-10) AND ($value+10)
ORDER BY rand() LIMIT 6
我想不出一个在SQL查询中做到这一点的好方法，如果不多次将项目表连接到自身上，这将随着表中项目数量的增长而导致组合爆炸。
我用PHP开发了一个解决方案，可以将您的商品分成价格组。考虑下表：
+----+--------------------+-------+
| 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 |
+----+--------------------+-------+
http://sqlfiddle.com/#！9/0d815
首先，根据项目的随机数量（在您的情况下，在3到6之间）将项目划分为价格组。价格组增量将由价格差异（20.00美元）除以所选项目的数量来确定。这样可以确保您不会超出您的差异范围。以下是一组4项的示例：
PRICE_GROUP_INCREMENT=方差/NUMBER_ITEMS
PRICE_GROUP_advincrement=20/4=5
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 |
+------------+-------------+
接下来，我们可以搜索结果集，找到与目标价格组相等的价格组组合。目标价格组由您的目标价格除以价格组增量确定。使用我们上面的例子，让我们试着找到4个项目，它们加起来是$35.00，差异是$20.00。
TARGET_PRICE_GROUP=圆形（TARGET_PRICE/PRICE_GROUP_INCREMENT）
TARGET_PRICE_GROUP=圆形（35.00/5.00美元）=7
通过搜索结果集，我们可以得到一个目标价格组7与这些组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脚本：
<?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";
}
}
这种算法方法的性能应该比纯基于SQL查询的解决方案要好得多，尤其是当项目表增长时。
您需要使用HAVING子句-
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
这是示例，这是另一个实例
在这种情况下，总和总是每个项目的总和（使用GROUP BY），如果每个项目只有一个，那就太好了。如果你有不止一个，那么总和将在GROUP BY中把所有这些项目加在一起。根据你最初的描述，这是你要寻找的第二个查询，客户可以在哪里看到价格范围内的随机产品。
如果您提供一个表模式（可能使用SQL Fiddle），然后向我们展示您希望的结果的示例，那将是最好的。