我需要从mysql表中选择记录,直到某列达到目标值为止, 假设我的桌子是

CREATE TABLE Table1 
    (
     id int auto_increment primary key,
     Date DateTime,
     type int, 
     MyAmountCol int
    );

INSERT INTO Table1
(Date, type, MyAmountCol)
VALUES
('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');

这是我正在尝试的SQL查询。

SELECT
  O.Id,
  O.Type,
  O.MyAmountCol,
  (SELECT
     sum(MyAmountCol) FROM Table1
   WHERE Id <= O.Id) 'RunningTotal'
FROM Table1 O
HAVING RunningTotal <=8

http://sqlfiddle.com/#!9/ec160/5

但无法按需工作,我们将不胜感激。

分析解答

在MySQL 8+和更高版本的MySQL中,可以使用窗口函数:

select t1.*
from (select t1.*,
             sum(t1.amount) over (order by t1.id) as running_amount
      from table1 t1
     ) t1
where running_amount <= 8

如果要使第一个达到或超过8,则:

where running_amount - amount < 8

在旧版本中,您可以使用相关的子查询或变量来执行此操作:

select t1.*
from (select t1.*,
             (select sum(tt1.amount)
              from table1 tt1
              where tt1.id <= t1.id
             ) as running_amount
      from table1 t1
     ) t1
where running_amount <= 8;

我更喜欢子查询而不是having子句,但这只是一个首选项。