将行限制为一列总和等于 MySQL 中的某个值

2021-11-20 00:00:00 sql mysql

我想编写一个查询，该查询返回所有行，直到其中一列值的总和达到某个值.

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.

相关文章