MySQL选择3个随机行，其中三行之和小于值

问题描述

我正在尝试从表中选择三个随机行，其中它们的组合item_price列小于所需的数量.

I am trying to select three random rows from a table, where their combined item_price column is less than a desired amount.

假设您有一个<input>美元金额.当您输入美元金额时，数据库将返回三个随机项目，它们的组合价格小于或等于您输入的美元金额.

Imagine you have an <input> for a dollar amount. When you enter the dollar amount, the database returns three random items, where their combined price is less than or equal to the dollar amount you enter.

如果我输入$ 300，您可以购买这三个商品，分别为$ 150，$ 100和$ 50.我很难创建一个查询，该查询将返回三个符合此条件的项目.

If I enter $300, you could buy these three items, $150, $100, and $50. I'm having difficulty creating a query that will return three items that meet this criteria.

SELECT t1.item_id, t1.item_price
FROM items t1
INNER JOIN items t2 ON ( t1.item_id = t2.item_id )
GROUP BY t1.item_id, t1.item_name, t1.item_price
HAVING SUM( t2.item_price ) <=300
ORDER BY RAND( )
LIMIT 3 

我认为这会起作用，但是我认为那只是巧合.它似乎只返回价格低于$ 300而不是总价低于$ 300的任何三件商品.

I thought this would work, but I think it was just a coincidence when it did. It seems to just return any three items whose prices are less than $300, not total less than $300.

我也尝试过以下查询:

SELECT t1.item_id, t1.item_price
FROM   items t1
JOIN   items t2 ON t2.item_id <= t1.item_id
WHERE  t2.item_price <= 500
GROUP  BY t1.item_id
HAVING SUM(t2.item_price) <= 500
ORDER  BY RAND()
LIMIT 3

同样，起初似乎可以工作，但随后它开始以2000美元的价格退货.

Again, seemed to work at first, but then it started returning items for $2000.

如果在PHP中有更好的方法(甚至牺牲性能)，我也不介意.我只是认为查询不会那么困难.

If there's a better (even sacrificing performance) way to do this in PHP, I don't mind. I just didn't think the query would be so difficult.

一如既往，感谢任何人的帮助.

As always, thanks anyone for the help.

推荐答案

这是另一种解决方案:

SELECT t1.item_id as id1, t2.item_id as id2, t3.item_id as i3
FROM items t1, items t2, items t3
WHERE
t1.item_id <> t2.item_id and
t1.item_id <> t3.item_id and
t2.item_id <> t3.item_id and
(t1.item_price + t2.item_price + t3.item_price) <= 300
order by rand()
limit 1

(可选)您可以按最小总和进行过滤

optionally you can filter by minimal sum

这篇关于MySQL选择3个随机行，其中三行之和小于值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！