查找缺失的序列空缺mysql

本文介绍了查找缺失的序列空缺mysql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的Column的值类似

I have Column with values like

MAC00006300
MAC00006301
MAC00006302
MAC00006305
.....
LED00006002
...........
LED00006008
ALD18965
ALD18967
ALD18968
.......
ALD19000

我需要两个值之间的缺失

I need the missing in between values

MAC00006303,MAC00006304 
LET00006003 ..... LET00006007
ALD18969,ALD18970.....ALD18989

SELECT NAME, VALUE + 1
FROM testmissingexampledata  mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    testmissingexampledata mi 
        WHERE   mi.VALUE = mo.VALUE + 1
        )
ORDER BY
        VALUE;

当我执行上面的查询时，我只能检索下一条丢失的记录.有人可以建议我如何找回所有丢失的记录吗?

When I executed the above query I was able to retrieve only next missing record. Can any one suggest to me how to retrieve all missing records?

推荐答案

如果没有将所有可能的数值的列表返回到LEFT JOIN，就无法返回丢失的行.

You can't return the rows that are missing without a list of all possible numerical values to LEFT JOIN against.

此查询(感觉它可能会更好/更强大/更快，但可以运行)将为您找到空白:

This query (which feels like it could be better/stronger/faster, but it works) will find you the gaps:

set @last_prefix = null;
set @last_value = null;
select result from (
    select @last_prefix, @last_value, name,
      @prefix := substring(name,1,3) as prefix,
      @value := substring(name,4) as value,
      case when @prefix = @last_prefix and @value != @last_value +1
        then concat ("gap from ", @prefix, ": ", @last_value+1, " to ", @value-1)
        else "ok" end as result,
      @last_prefix := @prefix, @last_value := @value
      from t20120921
) foo
where result != "ok";

这篇关于查找缺失的序列空缺mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！