获取缺少的数字 sql [英] Get missing number sql

问题描述

在表中我有字段 pwd ，它像这样 001 、 002 、 003 、 004 .

In table i have field pwd which goes like this 001 , 002, 003 , 004 .

为了获得最高价值，我这样做

To get highest value of that i go like this

 ("SELECT pwd FROM users WHERE pwd= (SELECT max(pwd) FROM users)")

这样我得到了数字 004.要增加它并再次添加到表中，我使用

That way i got number 004. To increment it and add again to table i use

biggest= CInt((SQLDataset.Tables(0).Rows(0).Item(0)))
Dim test As String = "000" & biggest
txtpwd.Text = test.Substring(test.Length - 3)
test2 = test.Substring(test.Length - 3)

但是如果我想找到第一个可用号码(缺少一个)怎么办.例如，如果我有

But what if i want to find the first available number ( missing one ) . Example if i have

001, 002, 003 ,005 , 006 , 007 , 009 , 013. 

我想获取号码004.我该怎么做.

I want to grab the number 004. How can i do that .

推荐答案

这样我将获得数组中所有缺失的数字，然后我使用行索引 0 只获得第一个.

This way i will get all of the missing numbers in array and then i use row index 0 to get only the first one.

select u1.pwd+1 as firstmissing
  from users as u1
       left outer join users as u2
                    on u2.pwd=u1.pwd+1
 where u2.pwd is null

这篇关于获取缺少的数字 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！