range quantity 20170318

DROP TABLE IF EXISTS tbl_products;
CREATE TABLE IF NOT EXISTS tbl_products(
    id INT PRIMARY KEY,
    quantity INT
);
INSERT INTO tbl_products
VALUES
(1,250),
(2,100),
(3,200),
(4,150),
(5,210);

SELECT * FROM tbl_products;

+----+----------+
| id | quantity |
+----+----------+
|  1 |      250 |
|  2 |      100 |
|  3 |      200 |
|  4 |      150 |
|  5 |      210 |
+----+----------+

//-- membuat range id tiap product
SELECT
    a.*, (SUM(b.quantity)-a.quantity+1) AS awal,SUM(b.quantity) AS akhir
FROM
    tbl_products a
    JOIN tbl_products b ON b.id<=a.id
GROUP
    BY a.id;

+----+----------+------+-------+
| id | quantity | awal | akhir |
+----+----------+------+-------+
|  1 |      250 |    1 |   250 |
|  2 |      100 |  251 |   350 |
|  3 |      200 |  351 |   550 |
|  4 |      150 |  551 |   700 |
|  5 |      210 |  701 |   910 |
+----+----------+------+-------+

//-- mencari id product pada posisi 401
SELECT
    c.id
FROM
    (
    SELECT
        a.*, (SUM(b.quantity)-a.quantity+1) AS awal,SUM(b.quantity) AS akhir
    FROM
        tbl_products a
        JOIN tbl_products b ON b.id<=a.id
    GROUP BY
        a.id
    ) c
WHERE
    401 BETWEEN c.awal AND c.akhir;

+----+
| id |
+----+
|  3 |
+----+