Oracle 从表中选择最大 id 返回空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8693981/
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
Oracle select max id from table returns null value
提问by user367134
I have the query:
我有查询：
SELECT MAX(prod_id) FROM products;
It returns the maximum value if there are records. But, if I truncate table and run the same query I am unable to get the maxid.
如果有记录，则返回最大值。但是，如果我截断表并运行相同的查询，我将无法获得maxID。
回答by Rohan
In case you want to query a table's column and suspect that the max function may return null, then you can return 0 in case null is encountered
如果您想查询表的列并怀疑 max 函数可能返回 null，那么您可以在遇到 null 的情况下返回 0
SELECT NVL(MAX(P.PROD_ID), 0) AS MAX_VAL
FROM PRODUCTS P
This will return at least 0 , if no value is encountered for the column that you mention ()
如果您提到的列没有遇到任何值，这将至少返回 0 ()
回答by Ben
Yes, by truncatingthe table you have removed all data in it, with no need for a commit. Therefore there is no data in the table and the maxof nothing is nothing.
是的，通过截断表，您已经删除了其中的所有数据，不需要commit. 因此表中没有数据max，虚无就是虚无。
回答by Gerald P. Wright
If you truncate the table, there are no rows left in the table. By definition, Max() returns NULL when run against an empty table.... or did I miss something here?
如果截断表，则表中将没有剩余行。根据定义，Max() 在针对空表运行时返回 NULL ......还是我在这里错过了什么？
回答by Florin Ghita
As Gerald P. Wright commented to an answer, if id is generated by a sequence, you can use it to find the value. But
正如 Gerald P. Wright 对答案所评论的那样，如果 id 是由序列生成的，您可以使用它来查找值。但
SELECT prod_id_seq.currval FROM DUAL
won't work, because currval works only in the session where you fetched a value with currval.
将不起作用，因为 currval 仅在您使用 currval 获取值的会话中有效。
so,
所以，
SELECT prod_id_seq.nextval FROM DUAL
can be an workaround for you, but would be a realy, realy BAD solution(if you get the id with this several times you'll get incremented values).
对您来说可能是一种解决方法，但将是一个非常非常糟糕的解决方案（如果您多次获得此 ID，您将获得递增的值）。
