在MySQL栏中获取最小的未使用值 [英] Get minimum unused value in MySQL column

问题描述

我有一个带有整数ID列的表.我想获得此列的最小未使用值.该查询应在表ID中找到第一个孔，并在其中获得最小值.我将尝试通过一些示例进行解释.

I have a table with integer ID column. I would like to get the minimum unused value for this column. The query should find the first hole in table IDs and get the minimum value inside it. I'll try to explain it with some examples.

示例1:无孔工作台

在这种情况下，我有一个没有孔的表，查询应该简单地获得最小的未使用值:应该得到:4

In this case, I have a table without holes and query should simply get the minimum unused value: should get: 4

|id|
|1 |
|2 |
|3 |

示例2:顶部有孔的桌子

在这种情况下，我们在顶部有一个洞(缺失值:1).查询找到孔并获取其中的最小值:应为1.

In this case, we have a hole on top (missing value: 1). The query finds the hole and gets the minimum value inside it: should get 1.

|id|
|2 |
|3 |
|4 |

在这种情况下，我们的顶部还有一个孔，但是里面有更多的缺失值(缺失值:1和2).查询找到孔并获取其中的最小值:应为1.

Also in this case, we have a hole on top, but we have more missing values inside it (missing values: 1 and 2). The query finds the hole and gets the minimum value inside it: should get 1.

|id|
|3 |
|4 |
|5 |

示例3:中间有孔的桌子

在这种情况下，我们在中间有一个洞(缺失值:2和3).查询找到孔并获取其中的最小值:应为2.

In this case, we have a hole in the middle (missing values: 2 and 3). The query finds the hole and gets the minimum value inside it: should get 2.

|id|
|1 |
|4 |
|5 |

示例4:在顶部和中间带有孔的桌子

在这种情况下，我们有多个孔:一个在顶部(缺失值:1)，一个在中间(缺失值:3).查询找到第一个孔并获得其中的最小值:应为1.

In this case, we have multiple holes: one on top (missing value: 1) and one in the middle (missing value: 3). The query finds the first hole and gets the minimum value inside it: should get 1.

|id|
|2 |
|4 |
|6 |

我已经尝试了 本文中提出的解决方案 strong> ，但在我的情况下无法正常工作.有什么想法吗?

I've tried the solution proposed in this post, but it doesn't work as expected in my case. Any ideas?

推荐答案

SELECT min(unused) AS unused
FROM (
    SELECT MIN(t1.id)+1 as unused
    FROM yourTable AS t1
    WHERE NOT EXISTS (SELECT * FROM yourTable AS t2 WHERE t2.id = t1.id+1)
    UNION
    -- Special case for missing the first row
    SELECT 1
    FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM yourTable WHERE id = 1)
) AS subquery

这篇关于在MySQL栏中获取最小的未使用值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！