mysql - 如何检查某些列中的缺失数字

我使用Mysql和spring数据jpa

我对如何找到某些列的缺失数据有疑问

该列属性为int。

所以。如果我在某些数据列中插入一些数据。

号码表

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

当我插入这样的数据时。

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,

如何使我缺少数字列

在spring data jpa本机查询中。

最佳答案

这个查询怎么样

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;

关于mysql - 如何检查某些列中的缺失数字，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57604196/