问题描述:

How is it possible to return rows with an accumulate sum for a row bigger or smaller than a specified value?

table:

id | count

-----------

1 | 30

2 | 10

3 | 5

4 | 20

5 | 15

query:

SELECT id, count

FROM table

ORDER BY id

HAVING SUM(count) < 50

return rows:

id | count

-------------

1 | 30

2 | 10

3 | 5

update

code:

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";

}

}

procedure:

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 ;

error:

#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 &amp;&amp; ' at line 12

网友答案:

The following query:

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;

produces the results:

id  count   Counter
1   30      30
2   10      40
3   5       45

I copied your table into MySql and called it "sumtest" btw. Please replace with your table name.

Effectively, we work out the running total, in id order, then use that as a subquery.

So this query:

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

Produces:

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.

EDIT: 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 [email protected]):

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 ;

Calling it this way:

SELECT Test_Cursing(50);

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);

Which returns:

id  count
1   30
2   10
3   5
相关阅读:
Top