选择max(x)返回null;如何使它返回0?

[英]SELECT max(x) is returning null; how can I make it return 0?

本文翻译自 Phillip Senn 查看原文 2009/11/06 81467 null/ returning/ make/ sql-server/ return/ sql

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)

7 个解决方案

#1

In SQL 2005 / 2008:

在SQL 2005 / 2008:

SELECT ISNULL(MAX(X), 0) AS MaxX
FROM tbl WHERE XID = 1

#2

or:

或者:

SELECT coalesce(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1

#3

Like this (for MySQL):

像这样(MySQL):

SELECT IFNULL(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1

For MSSQL replace IFNULL with ISNULL or for Oracle use NVL

对于MSSQL，将IFNULL替换为ISNULL或Oracle使用NVL。

#4

You can also use COALESCE ( expression [ ,...n ] ) - returns first non-null like:

您还可以使用合并(表达式[，…)-返回第一个非空值:

SELECT COALESCE(MAX(X),0) AS MaxX
FROM tbl
WHERE XID = 1

#5

Oracle would be

甲骨文将

SELECT NVL(MAX(X), 0) AS MaxX
FROM tbl
WHERE XID = 1;

#6

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不在那里工作。

#7

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

或

SELECT CASE MAX(X) WHEN NULL THEN 0 ELSE MAX(X) FROM tbl WHERE XID = 1

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2009/11/06/d3580aa2428ba3015083e21a8abb7521.html。

猜您在找

如果没有记录，如何使MySQL SUM查询返回零而不是null？ - How can I make a MySQL SUM query return zero instead of null if there are no records? 如何使form.select在Rails中返回一个模型？ - How can I make form.select return a model in Rails? 如果返回的值为Null，如何使PriorityBinding失败？ - How can I make a PriorityBinding fail if the value returned is Null? 如何让mySql返回选择JOIN with Count？ - How can I get mySql to return Select JOIN with Count? 当子查询什么都不返回时，我如何使0>全部返回false ? - How can I make 0>All() return false when the subquery returns nothing?

智能推荐