mysql 累计和_mysql – 查询中的累计和

以下查询：

SELECT * FROM

(SELECT id,

count,

@running_count := @running_count + count AS Counter

FROM sumtest, (SELECT @running_count := 0) AS T1 ORDER BY id) AS TableCount

WHERE TableCount.Counter < 50;

产生结果：

id count Counter

1 30 30

2 10 40

3 5 45

我将你的表复制到MySql中并称之为“sumtest”btw.请替换您的表名.

实际上,我们按id顺序计算运行总计,然后将其用作子查询.

所以这个查询：

SELECT id,

count,

@running_count := @running_count + count AS Counter

FROM sumtest, (SELECT @running_count := 0) AS T1

ORDER BY id

生产：

id count Counter

1 30 30

2 10 40

3 5 45

4 20 65

5 15 80

因此,通过对此计数器执行另一个选择来选择计数器小于所需总和的所有行,这将变得微不足道.

编辑：这是一个带光标的例子.我刚刚为你抛出这个函数(注意我的表叫做sumtest,我的帐号是默认的root @ localhost)：

DELIMITER $$

DROP FUNCTION IF EXISTS `Test_Cursing` $$

CREATE DEFINER=`root`@`localhost` FUNCTION `Test_Cursing`(_running_total_limit INT) RETURNS int

BEGIN

/* Why am I on StackOverflow at 01:41 on New Years Day. Dear oh dear, where's the beer? */

DECLARE _running_count INT default 0;

DECLARE _id INT;

DECLARE _current_id INT;

DECLARE _sum_count INT;

DECLARE _cur CURSOR FOR SELECT id, count FROM sumtest ORDER BY id;

OPEN _cur;

read_loop: LOOP

FETCH _cur INTO _id, _sum_count;

SET _running_count = _running_count + _sum_count;

IF _running_count > _running_total_limit THEN

LEAVE read_loop;

END IF;

SET _current_id = _id;

END LOOP;

CLOSE _cur;

RETURN _current_id;

END $$

DELIMITER ;

这样称呼它：

SELECT Test_Cursing(50);

将返回id = 3 – 也就是说,违反了运行总限制之前的最后一个id.然后你可以用它来：

SELECT * FROM sumtest WHERE id <= Test_Cursing(50);

哪个回报：

id count

1 30

2 10

3 5