SQL 如果没有找到记录，则返回一个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8098795/
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
Return a value if no record is found
提问by user1042304
I have this simple statement that works:
我有这个简单的声明有效：
SELECT idnumber FROM dbo.database WHERE number = '9823474'
If the number does not exist anywhere in the table, it fails. I would like to add something to this statement that says:
IF NO RECORD IS FOUND RETURN NULL INSTEAD OF NO ROW.
如果该数字不存在于表中的任何位置，则失败。我想在此声明中添加一些内容：
如果没有找到记录，则返回空值而不是行。
Any suggestions?
有什么建议？
回答by Erwin Brandstetter
Encapsulate the query in a sub-queryto transform "no row" to a NULL value.
将查询封装在子查询中以将“无行”转换为 NULL 值。
I tested and verified this with PostgreSQL, SQL Serverand MySQL. Also works with SQLite.
我使用PostgreSQL、SQL Server和MySQL测试并验证了这一点。也适用于SQLite。
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id;
In Oracleyou have to select from the dummy 1-row table DUALlike this:
在Oracle 中，您必须DUAL像这样从虚拟的 1 行表中进行选择：
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM DUAL;
You cando the same in MySQL for compatibility reasons, but you don't have to.
Similar in Firebird:
出于兼容性原因，您可以在 MySQL 中执行相同操作，但您不必这样做。
类似火鸟：
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM RDB$DATABASE;
This does it for DB2(like Sean commented):
这是针对DB2 的（就像Sean 评论的那样）：
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM SYSIBM.SYSDUMMY1;
Alternative with UNION ALL
替代 UNION ALL
SELECT id FROM tbl WHERE id = 9823474
UNION ALL
SELECT NULL -- FROM DUAL -- for Oracle
FETCH FIRST 1 ROW ONLY;
Standard SQL, but I only tested this with Postgres, which evaluates like this:
If a row is found in the first SELECT, it is returned. Postgres stops looking for more rows, as soon as the first is found due to LIMIT 1(FETCH FIRST 1 ROW ONLY).
The second SELECTis only even executed if the first returns nothing. The data type of the NULL value is determined by the data type of tbl.idautomatically.
标准 SQL，但我只用 Postgres 测试过，它的评估如下：
如果在第一个中找到一行SELECT，则返回它。Postgres 停止寻找更多的行，因为LIMIT 1( FETCH FIRST 1 ROW ONLY)找到第一行。仅当第一个不返回任何内容时才执行
第二个SELECT。NULL 值的数据类型由tbl.id自动确定的数据类型。
About the LIMITclause:
关于LIMIT条款：
回答by Sam Kort
To make it more simplier, this should work fine. If you assign this to a variable based on the datatype of your idnumber than you would be able to evaluate whether the value is null or the actual idnumber return.
为了使它更简单，这应该可以正常工作。如果您根据 idnumber 的数据类型将其分配给变量，那么您将能够评估该值是 null 还是实际的 idnumber 返回值。
SELECT ISNULL(
(
SELECT idnumber
FROM dbo.database
WHERE number = '9823474'
), NULL)
回答by Wilson
Select isnull(sum(Amount),0) as Amt from BeginningBalance where CustomerID = @CustomerID
Union all
Select isnull(sum(Amount),0) as Amt from SalesOrders where CustomerID = @CustomerID
Union all
Select isnull(sum(Amount),0) as Amt from SalesInvoices where CustomerID = @CustomerID
//Data Row Result if no data is present at Beginning Balance Table
// example
Amt
2000 // amount from sales orders
1000 // amount from sales invoices
// if the 1st select statement return no data use this
SELECT (select sum(Amount) from BeginningBalance
where CustomerID = @CustomerID) as Amt
Union all
Select sum(Amount) as Amt from SalesOrders where CustomerID = @CustomerID
Union all
Select sum(Amount) as Amt from SalesInvoices where CustomerID = @CustomerID
Result :
结果 ：
Amt
NULL // amount from BeginningBalance
2000 // amount from sales orders
1000 // amount from sales invoices
回答by Irfan Ashraf
I use this for MySql
我将它用于 MySql
SELECT IFNULL(ColumnA,"1") AS ColumnA , COUNT(1) AS Total FROM table
WHERE ID = 1 LIMIT 0, 1;
