MySQL查找小计 [英] MySQL finding subtotals
问题描述
编辑：
我被告知，让你们阅读意味着我不那么关注。我很抱歉。这是一个更简单的版本：
比尔从商店中得到价值100美元的物品。他想要返回足够的物品可以拿回30美元。
这家商店有一个Point of Return系统，可以帮助他做到这一点。
以下是他扫描其物品后的数据：
物品|价格|
袜子4.00
便宜电视22.00
胶带书9.00
纸上书7.00
派对帽3.00
相框10.00
锤子5.00
榨汁机16.00
mysql guide 24.00
总物品|总价格|
9 100.00
选项1
====== =========
item | price |
cheap tv 22.00
派对帽3.00
锤子5.00
========= ======
选项2
===============
物品|价格|
袜子4.00
pi cture frame 10.00
榨汁机16.00
===============
选项3
======== =======
物品|价格|
磁带上的书9.00
锤子5.00
榨汁机16.00
因为我完成了所有这些工作，所以我可能错过了几个选项。
最大的问题是：
有没有一种方法（使用GROUP BY，可能）有一个查询会返回任何可能的项目组合？
谢谢！
a
解决方案如果项目数量足够小，您可以用SQL强制执行此操作。这可能会很快写出解决方案，但您可能想要做更聪明的事情。听起来像是NP完整的背包问题。
如果项目数量很大，则需要深入研究动态编程算法。你必须问自己，这对你的申请有多重要。
如果项目数量相对较少，那么你可以对此进行暴力破解。一个蛮力SQL语句（这就是你所要求的）查找1,2或3个匹配项目的组合如下。如果这不能令人满意，那么SQL可能不适合这项工作。
SELECT
i1.id AS id1，
NULL AS id2，
NULL AS id3，
i1.amount
FROM
items i1
UNION ALL
SELECT
i1.id AS id1，
i2.id AS id2，
i3.id AS id3，
i1.amount + i2.amount AS total
FROM
项目i1，
项目i2
WHERE
i1.amount + i2.amount = 30 AND
i1.id< i2.id AND
i1.id<> i3.id
UNION ALL
SELECT
i1.id AS id1，
i2.id AS id2，
i3.id AS id3，
i1。 amount + i2.amount + i3.amount AS total
FROM
items i1，
items i2，
items i3
WHERE
i1.amount + i2 .amount + i3.amount = 30 AND
i1.id<> i2.id AND
i1.id<> i3.id AND
i2.id<> i3.id
在Oracle中，您可以使用CUBE函数将其转换为通用版本，而不是肯定与MySQL相当。
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.00I 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
解决方案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.
SELECT i1.id AS id1, NULL AS id2, NULL AS id3, i1.amount FROM items i1 UNION ALL SELECT i1.id AS id1, i2.id AS id2, i3.id AS id3, i1.amount + i2.amount AS total FROM items i1, items i2 WHERE i1.amount + i2.amount = 30 AND i1.id <> i2.id AND i1.id <> i3.id UNION ALL SELECT i1.id AS id1, i2.id AS id2, i3.id AS id3, i1.amount + i2.amount + i3.amount AS total FROM items i1, items i2, items i3 WHERE i1.amount + i2.amount + i3.amount = 30 AND i1.id <> i2.id AND i1.id <> i3.id AND i2.id <> i3.idIn Oracle, you would use the CUBE function to turn this into a generic version, not sure about a MySQL equivalent.
这篇关于MySQL查找小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
