I read a lot about MySQL gaps and islands problem, but I cannot find anything enough closer to understand my problem. I have gaps from zeros and islands from 15. You can see what I am talking about in the following tables The first table is my data:

CREATE TABLE gapsandislands (
  rownum int(11) NOT NULL,
  integer_id int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO gapsandislands (rownum, integer_id) VALUES
(1, 0),
(2, 0),
(3, 0),
(4, 0),
(5, 15),
(6, 15),
(7, 15),
(8, 15),
(9, 15),
(10, 15),
(11, 15),
(12, 15),
(13, 0),
(14, 0),
(15, 0),
(16, 0),
(17, 0),
(18, 0),
(19, 0),
(20, 0),
(21, 15),
(22, 15),
(23, 15),
(24, 15),
(25, 0),
(26, 0),
(27, 0);

My islands in this example are 5-12 and 21-24. But how can I managed them in a new table?

CodePudding user response：

if I got it right, this will help you

with no_zeroes as (
select rownum, integer_id, rownum - row_number() over(order by rownum) gaps_detector
  from gapsandislands
 where integer_id > 0
 )
 
select min(rownum) start_ruwnum, max(rownum) end_rownum
  from no_zeroes
 group by gaps_detector

In case you're on mysql version below 8 that don't support CTEs (the "with" part), copy query from the into the "from" part as a subquery

select min(rownum) start_ruwnum, max(rownum) end_rownum
  from (select rownum, integer_id, rownum - row_number() over(order by rownum) gaps_detector
          from gapsandislands
         where integer_id > 0)no_zeroes
 group by gaps_detector

Now, let's go to what's to be done in order to crack the problem you're facing.

1. Get rid of zeroes as you don't need them in the output anyway.
2. All you need to crack this problem is some value that will change from island to island. That's what "rownum - row_number()" is here for.

Do not hesitate to ask if you still have questions

Here's dbfiddle to check