查询中的累计金额 [英] accumulated sum in query
问题描述
对于大于或小于指定值的行，如何返回具有累加总和的行?
How is it possible to return rows with an accumulate sum for a row bigger or smaller than a specified value?
id | count
-----------
1 | 30
2 | 10
3 | 5
4 | 20
5 | 15
查询:
SELECT id, count
FROM table
ORDER BY id
HAVING SUM(count) < 50
返回行:
id | count
-------------
1 | 30
2 | 10
3 | 5
更新
代码:
public function query(){
switch($this->table){
case 'in_stock':
return "SELECT * FROM ".Init::$static['db'].".stock
WHERE id<=dynaccount.stock_first_available_id(".$this->value['num_stock'].", ".$this->value['product_id'].", ".(isset($this->att_arr['gid']) ? $this->att_arr['gid']:$_SESSION['gid']).")
ORDER BY time, id";
}
}
程序:
DELIMITER $$
DROP FUNCTION IF EXISTS `stock_first_available_id` $$
CREATE DEFINER=`dynaccount`@`localhost` FUNCTION `stock_first_available_id`(_running_total_limit INT, _product_id INT, _group_id INT) RETURNS INT
BEGIN
DECLARE _running_count INT default 0;
DECLARE _id INT;
DECLARE _current_id INT;
DECLARE _sum_count INT;
IF (SELECT COUNT(*) FROM stock WHERE group_id=_group_id && type=2 && product_id=_product_id) = 0 THEN
RETURN 0;
END IF;
DECLARE _cur CURSOR FOR SELECT id, count FROM stock WHERE group_id=_group_id && type=2 && product_id=_product_id ORDER BY time DESC, id DESC;
OPEN _cur;
read_loop: LOOP
FETCH _cur INTO _id, _sum_count;
SET _running_count = _running_count + _sum_count;
SET _current_id = _id;
IF _running_count > _running_total_limit THEN
LEAVE read_loop;
END IF;
END LOOP read_loop;
CLOSE _cur;
RETURN _current_id;
END $$
DELIMITER ;
错误:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE _cur CURSOR FOR SELECT id, count FROM stock WHERE group_id=_group_id && ' at line 12
推荐答案
以下查询:
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".请替换为您的表格名称.
I copied your table into MySql and called it "sumtest" btw. Please replace with your table name.
有效地，我们以id顺序计算出运行总计，然后将其用作子查询.
Effectively, we work out the running total, in id order, then use that as a subquery.
所以这个查询:
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
因此，通过对此进行另一次选择来选择计数器小于所需总和的所有那些行变得不那么重要了.
So then it becomes a trivial matter to select all those rows where the counter is less than your desired sum by performing another select on this.
这是一个带有游标的示例.我刚刚为您一起使用了此功能(请注意，我的表称为sumtest，我的帐户是默认的root @ localhost):
Here is an example with a cursor. I've just thrown this function together for you (note my table is called sumtest and my account is the default 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.然后，您可以将其用于:
will return id = 3 - that is, the last id before the running total limit is breached. You can then use this to:
SELECT * FROM sumtest WHERE id <= Test_Cursing(50);
哪个返回:
id count
1 30
2 10
3 5
这篇关于查询中的累计金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
