以下查询:
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”。请替换为您的表名。
实际上,我们按 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