php - 如何在mysql中查找具有多行总和的id？

Question

这是我的 mysql 表。

CREATE TABLE IF NOT EXISTS  tbl_money  (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   amount  int(11) NOT NULL,
   used  int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

INSERT INTO  tbl_money  ( id ,  amount ,  used ) VALUES
(8, 2, 0),
(9, 4, 0),
(10, 4, 0),
(11, 3, 0),
(12, 8, 0),
(13, 10, 0),
(14, 13, 0);

Id 是一个主键字段，money 可以是从 1 到任意数字的任意数字。

问题:假设我必须从金额为 8 的表中找到 id。这很简单，因为 id 5 包含金额 8。但是如果我必须找到金额为 14 的 id，则不存在。我现在必须选择 2 个或更多字段，总和等于 14。例如，在上表中，我可以选择 id 6 和 id 3，总和等于 14。就像我需要找到 6 一样，我必须选择id 1 和 2 因为它们的金额之和等于 6。

有时我们可能需要选择超过 2 行才能使总和相等。但是，如果任何条件与表的所有行都不匹配，我们可以返回 0。

条件:已被占用的行不能再次选择。使用的字段应始终为 0。如果使用的字段值为 1，我们将无法选择它。我们只会搜索偶数，所以我们不需要担心奇数。

请建议我如何解决这个算法。提前致谢。

Answer 1

纯 SQL 方式来做，这可能不是那么有效:-

SELECT CONCAT_WS(',', a.id, b.id, c.id, d.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id
INNER JOIN tbl_money c ON c.id > b.id
INNER JOIN tbl_money d ON d.id > c.id
WHERE a.amount + b.amount + c.amount + d.amount = 14
UNION
SELECT CONCAT_WS(',', a.id, b.id, c.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id
INNER JOIN tbl_money c ON c.id > b.id
WHERE a.amount + b.amount + c.amount = 14
UNION
SELECT CONCAT_WS(',', a.id, b.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id
WHERE a.amount + b.amount = 14
UNION
SELECT a.id
FROM tbl_money a
WHERE a.amount = 14

编辑 - 修改以检查使用的字段，并且金额不是奇数

SELECT CONCAT_WS(',', a.id, b.id, c.id, d.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id AND b.used = 1 AND MOD(b.amount, 2) = 0
INNER JOIN tbl_money c ON c.id > b.id AND c.used = 1 AND MOD(c.amount, 2) = 0
INNER JOIN tbl_money d ON d.id > c.id AND d.used = 1 AND MOD(d.amount, 2) = 0
WHERE a.amount + b.amount + c.amount + d.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0
UNION
SELECT CONCAT_WS(',', a.id, b.id, c.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id AND b.used = 1 AND MOD(b.amount, 2) = 0
INNER JOIN tbl_money c ON c.id > b.id AND c.used = 1 AND MOD(c.amount, 2) = 0
WHERE a.amount + b.amount + c.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0
UNION
SELECT CONCAT_WS(',', a.id, b.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id AND b.used = 1 AND MOD(b.amount, 2) = 0
WHERE a.amount + b.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0
UNION
SELECT a.id
FROM tbl_money a
WHERE a.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0