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

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

Imagine you have an 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.

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

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.

I also tried this query:

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

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

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.

解决方案

here is another solution:

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