Geek asks and answers

The accumulated amount in the request

How can I return rows with an accumulation sum for a row greater than or less than a given value?

tables:

id | count
-----------
1 | 30
2 | 10
3 | 5
4 | 20
5 | 15

inquiry:

SELECT id, count
FROM table
ORDER BY id
HAVING SUM(count) < 50

return lines:

id | count
-------------
1 | 30
2 | 10
3 | 5

Update

the 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
+3
source share
1 answer

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;

gives the results:

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

I copied your table to MySql and named it "sumtest". Please replace the name of your table.

Effectively, we determine the current total in order of id, and then use it 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

It produces:

id  count   Counter
1   30      30
2   10      40
3   5       45
4   20      65
5   15      80

So, it becomes a trivial question to select all those lines where the counter is less than your desired amount by making another choice.

EDIT: . ( , 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 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
+6

More articles:


All Articles