将行限制为MySQL中某一列的总和等于某个值的位置 [英] limiting the rows to where the sum a column equals a certain value in 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.

这篇关于将行限制为MySQL中某一列的总和等于某个值的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！