SQL SELECT max(x) 返回空值；我怎样才能让它返回0？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1688715/
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(x) is returning null; how can I make it return 0?
提问by Phillip Senn
How do you return 0 instead of null when running the following command:
运行以下命令时如何返回 0 而不是 null：
SELECT MAX(X) AS MaxX
FROM tbl
WHERE XID = 1
(Assuming there is no row where XID=1)
（假设没有 XID=1 的行）
回答by HLGEM
or:
或者：
SELECT coalesce(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1
回答by Nestor
In SQL 2005 / 2008:
在 SQL 2005 / 2008 中：
SELECT ISNULL(MAX(X), 0) AS MaxX
FROM tbl WHERE XID = 1
回答by Greg
Like this (for MySQL):
像这样（对于 MySQL）：
SELECT IFNULL(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1
For MSSQL replace IFNULLwith ISNULLor for Oracle use NVL
对于 MSSQL 替换IFNULL为ISNULL或为 Oracle 使用NVL
回答by Mark Schultheiss
You can also use COALESCE ( expression [ ,...n ] ) - returns first non-null like:
您还可以使用 COALESCE ( expression [ ,...n ] ) - 返回第一个非空值，如：
SELECT COALESCE(MAX(X),0) AS MaxX
FROM tbl
WHERE XID = 1
回答by Jim
Oracle would be
甲骨文会
SELECT NVL(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1;
回答by Hassan Ali Shahzad
For OLEDB you can use this query:
对于 OLEDB，您可以使用以下查询：
select IIF(MAX(faculty_id) IS NULL,0,MAX(faculty_id)) AS max_faculty_id from faculties;
As IFNULL is not working there
由于 IFNULL 在那里不起作用
回答by Larry Lustig
Depends on what product you're using, but most support something like
取决于您使用的产品，但大多数支持类似
SELECT IFNULL(MAX(X), 0, MAX(X)) AS MaxX FROM tbl WHERE XID = 1
or
或者
SELECT CASE MAX(X) WHEN NULL THEN 0 ELSE MAX(X) FROM tbl WHERE XID = 1
