Grouping continuous range POC

https://www.facebook.com/groups/ThaiPGAssociateSociety/permalink/1675605072650755/

first create table

CREATE TABLE mb_master_barcode (
    id int NOT NULL AUTO_INCREMENT,
    barcode_prefix int,
    barcode_code int,
    barcode_status int,
    PRIMARY KEY (id)
);

Then populate some data

INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070000, 1);
INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070001, 1);
INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070002, 1);
INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070004, 1);
INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070005, 1);
INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070006, 1);
INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070008, 1);
INSERT INTO `mb_master_barcode`(`barcode_prefix`, `barcode_code`, `barcode_status`) VALUES (10, 1070009, 1);

Use query

SELECT 
    LPAD(BEGIN, 8, "0" ) AS START,
    LPAD(MAX(barcode_code), 8, "0" ) AS END,
    (MAX(barcode_code) - BEGIN ) + 1 AS QTY
FROM (
    SELECT mb_master_barcode.*,
    @f:= IF((barcode_code - @c) = 1, @f, barcode_code) AS BEGIN,
    @c:=barcode_code, @i:=id
    FROM mb_master_barcode JOIN (SELECT @i:=NULL) AS init
    ORDER BY barcode_code
) as t
GROUP BY BEGIN;

result

+----------+----------+------+
| START    | END      | QTY  |
+----------+----------+------+
| 01070000 | 01070002 |    3 |
| 01070004 | 01070006 |    3 |
| 01070008 | 01070009 |    2 |
+----------+----------+------+
3 rows in set, 4 warnings (0.00 sec)

powerdefy/GCR.md

Grouping continuous range POC