How to check missing number in some column

Question

I use Mysql and spring data jpa

I Have Questions about how to find missing data some column

that column attribute is int.

So.if i insert some data in some data column.

number table

seq | number | sometext | someData | something | some | some

when i insert data like this.

1 0 sometext someData  something some some,

2 2 sometext someData  something some some,

3 3 sometext someData  something some some,

4 5 sometext someData  something some some,

5 10 sometext someData  something some some,

how to get i missing number column

in spring data jpa native query.

Answer 1

How about this query

SELECT   CONCAT(X.EXPECTED, IF(X.GOT-1>X.EXPECTED, CONCAT(' ~ ',X.GOT-1), '')) AS MISSING
FROM     (
         SELECT
                  @ROWNUM:=@ROWNUM+1 AS EXPECTED,
                  IF(@ROWNUM=B.number , 0, @ROWNUM:=B.number ) AS GOT
         FROM     (SELECT @ROWNUM:=0) AS A
                  INNER JOIN YOUR_TABELE B
         ORDER BY B.number 
         ) AS X
WHERE X.GOT!=0;