这可能有点令人困惑,但请原谅我。是这样的:
我有一个包含~1000条记录的数据库,如下表所示:
+------+----------+----------+
| id | date | amount |
+------+----------+----------+
| 0001 | 14/01/15 | 100 |
+------+----------+----------+
| 0002 | 14/02/04 | 358 |
+------+----------+----------+
| 0003 | 14/05/08 | 1125 |
+------+----------+----------+
我想做的是:
检索从2014年到昨天的所有记录:
WHERE `date` > '14-01-01' AND `date` < CURDATE()
但也得到
amount
到当前日期的总和,这是:WHERE `date` < CURDATE()
我已经通过选择基于第二个条件的所有记录,获得总和,然后排除那些与第一个条件不匹配的记录,得到了这个工作。像这样:
SELECT `id`, `date`, `amount` FROM `table`
WHERE `date` < CURDATE()
然后:
$rows = fetchAll($PDOStatement);
foreach($rows as $row) {
$sum += $row->amount;
if (
strtotime($row->date) > strtotime('14-01-01') &&
strtotime($row->date) < strtotime(date('Y-m-d'))
) {
$valid_rows[] = $row;
}
}
unset $rows;
是否有一种方法可以在单个查询中有效地实现这一点?一个事务会比在PHP中整理记录更有效吗?这必须符合sql标准(我将在MySQL和SQLite上这样做)。
更新:如果结果是这样的也没关系:
+------+----------+----------+-----+
| id | date | amount | sum |
+------+----------+----------+-----+
| 0001 | 14/01/15 | 100 | 458 |
+------+----------+----------+-----+
| 0002 | 14/02/04 | 358 | 458 |
+------+----------+----------+-----+
| 0003 | 14/05/08 | 1125 | 458 |
+------+----------+----------+-----+
最坏的情况是,当结果集最终与给出总和的集合相同时(在这种情况下,附加总和将无关紧要,并且会导致开销),但对于任何其他常规情况,带宽节省将是巨大的。
你可以用你的和创建一个特殊的记录,并在你的第一个查询的末尾添加它
SELECT * FROM `table` WHERE `date` > '14-01-01' AND `date` < CURDATE()
UNION
SELECT 9999, CURDATE(), SUM(`amount`) FROM `table` WHERE `date` < CURDATE()
然后你会得到所有你想要的记录和id为9999的记录或者是你的总和
这可以通过相关子查询实现,如下所示:
SELECT *, (SELECT SUM(amount) FROM t WHERE t.date < t1.date) AS PrevAmount
FROM t AS t1
WHERE `date` > '14-01-01' AND `date` < CURDATE()
这很粗俗,但是:
> select * from foo;
+------+------+
| id | val |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+------+------+
5 rows in set (0.02 sec)
> select * from foo
left join (
select sum(val)
from foo
where id < 3
) AS bar ON 1=1
where id < 4;
+------+------+----------+
| id | val | sum(val) |
+------+------+----------+
| 1 | 1 | 3 |
| 2 | 2 | 3 |
| 3 | 3 | 3 |
+------+------+----------+
基本上,在连接子查询中进行求和。这会将求和结果附加到外部表结果中的每一行。您将浪费一些带宽,将每一行的重复值发送出去,但它确实可以在"单个"查询中获得结果。
EDIT:
您可以使用LEFT OUTER JOIN获得SUM。
SELECT t1.`id`, t1.`date`, t2.sum_amount
FROM
`table` t1
LEFT OUTER JOIN
(
SELECT SUM(`amount`) sum_amount
FROM `table`
WHERE `date` < CURDATE()
) t2
ON 1 = 1
WHERE t1.`date` > STR_TO_DATE('01,1,2014','%d,%m,%Y') AND t1.`date` < CURDATE();
你想让它做什么就做什么…优化子查询才是真正的挑战:
SELECT id,date,amount,(SELECT SUM(amount) FROM table) AS total_amount
FROM table
WHERE date BETWEEN '14-01-01' AND DATE_ADD(CURDATE(), INTERVAL -1 DAY)