oracle 选择表中没有行的列的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4900896/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select max value of a column in table with no rows
提问by changed
I am using oracle database
我正在使用oracle数据库
While inserting a row in a table, i need to find the max value of a column and increment it by 1, and use that value in row i am inserting.
在表中插入一行时，我需要找到一列的最大值并将其增加 1，然后在我插入的行中使用该值。
INSERT INTO dts_route
(ROUTE_ID, ROUTE_UID, ROUTE_FOLDER)
VALUES (
(SELECT MAX(ROUTE_ID) + 1 FROM route) ,
ROUTE_UID,
ROUTE_FOLDER)
This works fine if their is at least one entry in table. But returns null when their are no entries in table.
如果它们至少是表中的一个条目，则这可以正常工作。但是当它们在表中没有条目时返回 null。
How can i get default value of 1 when their are no entries in table.
当表中没有条目时，我如何获得默认值 1。
回答by codymanix
SELECT COALESCE(MAX(ROUTE_ID),0) ...
回答by Ilya Kogan
This is not a safe way of creating an auto-increment field. You can use an Oracle sequence to achieve this goal.
这不是创建自动增量字段的安全方法。您可以使用 Oracle 序列来实现此目标。
As for the null, you can use NVLto give a default value (say, 0) in case the function returns null.
至于空值，您可以使用NVL给出一个默认值（比如 0），以防函数返回空值。
回答by Chirag Tayal
Use sequence for the ID. You need to create sequence. See below link
使用序列作为 ID。您需要创建序列。见下面的链接
http://www.basis.com/onlinedocs/documentation/b3odbc/sql_sequences.htm
http://www.basis.com/onlinedocs/documentation/b3odbc/sql_sequences.htm
回答by OMG Ponies
Use:
用：
INSERT INTO dts_route
(ROUTE_ID)
SELECT COALESCE(MAX(r.route_id), 0) +1
FROM ROUTE r
...but you really should be using a sequenceto populate the value with a sequential numeric value:
...但你真的应该使用一个序列来用一个连续的数值填充值：
CREATE SEQUENCE dts_route_seq;
...
...
INSERT INTO dts_route
(ROUTE_ID)
SELECT dts_route_seq.NEXTVAL
FROM DUAL;
回答by Mark Baker
Set a default for NULL
为 NULL 设置默认值
SELECT NVL(MAX(ROUTE_ID),0)
though using a sequence might be easier if you don't mind the odd gaps in your route ids
尽管如果您不介意路线 ID 中的奇怪间隙，使用序列可能会更容易
回答by PyreneesJim
If you are concerned about there being gaps in your route ids then create the sequence with the NOCACHE clause:
如果您担心路由 ID 中存在间隙，请使用 NOCACHE 子句创建序列：
CREATE SEQUENCE dts_route_seq NOCACHE;
Note that there is a performance hit because Oracle now has to "commit" each time you increment the sequence.
请注意，性能受到影响，因为现在每次增加序列时，Oracle 都必须“提交”。
