MySQL 查找小计
编辑：
有人告诉我，让你们阅读意味着我得到的关注更少。 我很抱歉。 这是一个更简单的版本：
比尔从一家商店购买了价值 100 美元的商品。
他想要退回足够多的物品，以恰好拿回 30 美元。
商店有一个退货点系统可以帮助他做到这一点。
这是他扫描物品后的数据：
item ¦ price ¦
socks 4.00
cheap tv 22.00
book on tape 9.00
book on paper 7.00
party hats 3.00
picture frame 10.00
hammer 5.00
juicer 16.00
mysql guide 24.00
total items ¦ total price ¦
9 100.00
Option 1
===============
item ¦ price ¦
cheap tv 22.00
party hats 3.00
hammer 5.00
===============
Option 2
===============
item ¦ price ¦
socks 4.00
picture frame 10.00
juicer 16.00
===============
Option 3
===============
item ¦ price ¦
book on tape 9.00
hammer 5.00
juicer 16.00
我可能错过了一些选项，因为所有这些都是我编造的。
所以，最大的问题是：
有没有一种方法（可能使用 GROUP BY）让一个查询返回所有可能的项目组合？
谢谢！
A
EDIT:
I'm told that making you guys read means I get less attention. My apologies. Here's a simpler version:
Bill got $100 dollars worth of items from a store.
He wants to return enough of the items to get exactly $30 dollars back.
The store has a Point of Return system that will help him do this.
Here is the data after he scans his items:
item ¦ price ¦
socks 4.00
cheap tv 22.00
book on tape 9.00
book on paper 7.00
party hats 3.00
picture frame 10.00
hammer 5.00
juicer 16.00
mysql guide 24.00
total items ¦ total price ¦
9 100.00
Option 1
===============
item ¦ price ¦
cheap tv 22.00
party hats 3.00
hammer 5.00
===============
Option 2
===============
item ¦ price ¦
socks 4.00
picture frame 10.00
juicer 16.00
===============
Option 3
===============
item ¦ price ¦
book on tape 9.00
hammer 5.00
juicer 16.00
I probably missed a few options, since I made all of this up.
So, the big question is:
Is there a way (with GROUP BY, probably) to have one query that would return ever possible combination of items?
Thanks!
a
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您要求提供所有子集，总金额正好为 30 美元。
这听起来很像子集和问题，以及背包问题，所以我强烈怀疑你是否可以通过简单的查询来做到这一点。 您可能必须转向 T-SQL，但即使这样也可能看起来很难看。
我认为编程是解决这个问题的方法。
You're asking for all subsets which sum up to exactly $30.
This sounds a lot like the subset sum problem, and knapsack problem, so I strongly doubt you can do this with a simple query. You'd probably have to turn to T-SQL, but even that would probably look ugly.
I think programming is the way to go here.
如果项目数量足够小，您可以使用 SQL 强制执行此操作。 这可能是一个快速编写的解决方案，但您可能想做一些更聪明的事情。 听起来像是 NP 完全的“背包问题”。
如果项目数量很大，则需要深入研究动态规划算法。 你必须问自己这对你的申请有多重要。
如果项目数量相对较少，您可能可以暴力破解。 查找 1,2 或 3 个匹配项的组合的强力 SQL 语句（这正是您所要求的）如下所示。 如果这不能令人满意，那么 SQL 可能不是适合这项工作的工具。
在 Oracle 中，您可以使用 CUBE 函数将其转换为通用版本，不确定 MySQL 是否等效。
If the number of items are small enough you can brute force this with SQL. This might be a quick to write solution, but you probably want to do something smarter. Sounds like the "knapsack problem" which is NP complete.
If the number of items is large, you will need to delve into dynamic programming algorithms. You have to ask yourself how important this is to your application.
If the number of items is relatively small, you may be able to brute-force this. A brute-force SQL statement (which is what you asked for) that finds combinations of 1,2 or 3 items that match is as follows. If this is not satisfactory, then maybe SQL is not the right tool for this job.
In Oracle, you would use the CUBE function to turn this into a generic version, not sure about a MySQL equivalent.
我不认为 group by 可以做到这一点。
我想不出比查找/尝试所有排列更好的方法，无论是使用您选择的编程语言还是使用存储过程。
如果您的物品超过 30 美元，您可以忽略它们。
如果您想要根据某些标准选择“最佳”选项，则会有一些改进。
i don't think group by can do it.
i can't think of a better way than finding/trying all of the permutations, either in your programming language of choice or with a stored procedure.
if you had items over 30$ you could omit them.
there would be some improvements if you wanted a "best" option by some criteria.
这个问题实际上是P/NP问题。 例如，如果不进行强力搜索，您可能无法找到最合适的商品价格，并且如果您的客户商店很大 - 您会发现搜索非常持久。
你可以使用一些现有的算法，它们可以给你很好的猜测，但恐怕它们都是非 SQL 的。
我建议对您的问题进行近似：
创建过程/查询来查找最适合所需价格的一篇文章，然后再次调用它以获取新的更改。 不完美，但可以完成工作。
This problem is in fact P/NP. eg you probably can't find THE BEST fitting article prices, without brute force search, and if your clients store is big - you can find that search very long lasting.
You can use some of the existing algorithms that can give you pretty good guess, but I'm afraid that they are all non-SQL ones.
I would suggest to do approximation of your problem:
Create procedure/query that finds ONE article that best fits wanted price, and than call it again for new change you have. Not perfect, but will do the job.