我想编写一个查询,该查询返回所有行,直到其中一列值的总和达到某个值.
I want to write a query which returns all rows until the sum of one of the columns value reaches a certain value.
例如下表:
DATE ETC Meeting
2013-02-01 00:00:00 85482 1
2013-02-01 00:00:00 47228 2
2013-02-02 00:00:00 12026 4
2013-02-03 00:00:00 78927 6
2013-02-04 00:00:00 85662 2
2013-03-05 00:00:00 47978 1
2013-08-07 00:00:00 8582 1
如果我想得到行,直到 Meeting
列的总和等于 7.
If I want to get the rows until the sum of column Meeting
equals 7.
DATE ETC Meeting
2013-02-01 00:00:00 85482 1
2013-02-01 00:00:00 47228 2
2013-02-02 00:00:00 12026 4
如果我想得到行,直到 Meeting
列的总和等于 13.
If I want to get the rows until the sum of column Meeting
equals 13.
DATE ETC Meeting
2013-02-01 00:00:00 85482 1
2013-02-01 00:00:00 47228 2
2013-02-02 00:00:00 12026 4
2013-02-03 00:00:00 78927 6
这是一种应该在 MySQL 中工作的方法:
Here's a way which should work in MySQL :
SELECT
O.Id,
O.Type,
O.MyAmountCol,
(SELECT
sum(MyAmountCol) FROM Table1
WHERE Id <= O.Id) 'RunningTotal'
FROM Table1 O
HAVING RunningTotal <= 7
它涉及计算运行总数并选择运行总数小于或等于给定数字的记录,在本例中为7
.
It involves calculating a running total and selecting records while the running total is less than or equal to the given number, in this case 7
.
这篇关于将行限制为一列总和等于 MySQL 中的某个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!