SQL-查找最低未使用的数字 [英] SQL - Find the lowest unused number

问题描述

我在2个不同的表中都有一个数字"列.这不是ID.

I have a 'number' column in 2 different tables. This isn't the ID.

我已经创建了一个工会，像这样:

I have created a union like so:

SELECT number FROM table1 UNION SELECT number FROM table ORDER BY number ASC

结果如下:

number
=====
1
2
3
5
6
8

如何找到未使用的最低号码?在这种情况下，它将是4.一旦使用了4，它将是7，以此类推

How can I find the lowest unused number? In this case it would be 4. Once 4 has been used it would be 7, etc etc

推荐答案

假设您的电话号码始终从1开始，下面的查询将给出未使用的电话号码

Assuming your number start from 1 always below query will give unused number

select min(rank) as Num from 
(select num,@curRank1 := @curRank1 + 1 AS rank from (SELECT num1 as num FROM t1 
UNION
SELECT num2 as num FROM t2) a1, (SELECT @curRank1 := 0) r ORDER BY num ASC) tab where num != rank;`

这篇关于SQL-查找最低未使用的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！