SQL -手动获取给定格式的下一个可用ID

Question

我试图构建一个简单的SQL SELECT语句，该语句手动提供给定格式的下一个唯一ID。

示例见下表

-- Students (Table)
-- ID - Not a primary key
-- Type - Numbering format
+----------+------+
|    ID    | Type |
+----------+------+
|        1 | M    |
|        2 | M    |
|        5 | M    |
|     7056 | F    |
|     7057 | F    |
|     7058 | F    |
|     7090 | F    |
|     7091 | N    |
|    10910 | N    |
|    10911 | N    |
| 99000000 | O    |
| 99000001 | O    |
+----------+------+

-- Some of the available values:
+---+------+-------+----------+
| M |  F   |   N   |    O     |
+---+------+-------+----------+
| 6 | 7092 | 10912 | 99000002 |
| 7 | 7093 | 10913 | 99000003 |
| 8 | 7094 | 10914 | 99000004 |
+---+------+-------+----------+

在这里，假设我想获得'7092'的类型'F'作为下一个值。但是如果我使用MAX函数,它将返回"99000002"。

SELECT MAX(id)+1 FROM students;

如果我使用类型列，我可以为类型'F'获得'7091'，但这是不可用的，因为它被另一个类型使用。

SELECT MAX(id)+1 FROM students WHERE type = 'F';

SELECT MAX(id)+1 
FROM   students 
WHERE  type = 'F'
AND id NOT IN ( SELECT DISTINCT id FROM students)

我无法更改数据库结构。如果是这样，对于给定的场景，是否有一种方法(单个SELECT语句)来获取所选类型(例如:类型F)的下一个可用ID ?
我用Oracle 10 g,但MySQL和SQL Server是好的。

CREATE TABLE students (
id   NUMBER,
type CHAR(1)
);
INSERT INTO students VALUES (1, 'M');
INSERT INTO students VALUES (2, 'M');
INSERT INTO students VALUES (5, 'M');
INSERT INTO students VALUES (7056, 'F');
INSERT INTO students VALUES (7057, 'F');
INSERT INTO students VALUES (7058, 'F');
INSERT INTO students VALUES (7090, 'F');
INSERT INTO students VALUES (7091, 'N');
INSERT INTO students VALUES (10910, 'N');
INSERT INTO students VALUES (10911, 'N');
INSERT INTO students VALUES (99000000, 'O');
INSERT INTO students VALUES (99000001, 'O');

我将非常感激任何帮助。无论如何，谢谢你抽出时间。

Answer 1

您正在寻找空白，因此您正在寻找id列表中的值NOT IN。

下面的查询将为您提供表中所有id的NOT IN列表。

SELECT sub_id FROM (
SELECT id+1 as sub_id FROM students
)sub_table WHERE sub_id NOT IN (
SELECT id FROM students
);

然后您可以选择该列表中的MIN():

SELECT MIN(sub_id) FROM (
SELECT id+1 as sub_id FROM students
)sub_table WHERE sub_id NOT IN (
SELECT id FROM students
);

请记住，只有当你已经有了至少一个id时，它才会起作用，除非你已经有了id 1，否则它不会从1开始。

注意:第一个查询只给你所有空格的列表加上下一个可用的id。

Answer 2

您可以使用:

SELECT COALESCE(MAX(id) + 1,1) AS next_id
FROM   (
SELECT id,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM   students
)
WHERE id = rn;

或:

SELECT COALESCE(MAX(id + 1), 1) AS next_id
FROM   students
WHERE  CONNECT_BY_ISLEAF = 1
START WITH id = 1
CONNECT BY PRIOR id + 1 = id;

对于您的示例数据，两者都输出:

<表类>NEXT_IDtbody><<tr>3

在where后使用group by typeSELECT MAX(id)+1 FROM student WHERE type = 'F' GROUP BY type