我有一个简单的语句可以工作:
SELECT idnumber FROM dbo.database WHERE number = '9823474'
如果表中不存在该数字,则失败。我想在这个语句中添加一些内容,以便说:
如果没有找到记录,则返回 NULL 而不是无行。
有什么建议吗?
将查询封装在子查询中,以将“无行”转换为null
值。
我已使用PostgreSQL, SQLite, SQL Server和MySQL进行了测试和验证。
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id;
DUAL
中进行选择:SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM DUAL;
出于兼容性的考虑,您可以在MySQL中执行相同的操作,但您不必这样做。
在Firebird中类似:
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM RDB$DATABASE;
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM SYSIBM.SYSDUMMY1;
SELECT
列表中的NULL
值。这是逻辑上的必要性。 - Erwin BrandstetterSELECT
,甚至不需要一个函数。 - Erwin BrandstetterSELECT ISNULL(
(
SELECT idnumber
FROM dbo.database
WHERE number = '9823474'
), NULL)
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
结果:
Amt
NULL // amount from BeginningBalance
2000 // amount from sales orders
1000 // amount from sales invoices
我用这个来操作 MySql
SELECT IFNULL(ColumnA,"1") AS ColumnA , COUNT(1) AS Total FROM table
WHERE ID = 1 LIMIT 0, 1;
我最简单的方法是使用老式的IF THEN ELSE
技巧!适用于所有SQL语言。
IF EXISTS (SELECT * FROM dbItem WHERE price >= 10)
BEGIN
SELECT * FROM dbItem WHERE price >= 10
END
ELSE
SELECT 'No record'
这里是一个独立的Oracle概念验证,它返回一个真实值而不是NULL
在Oracle中,“dual”表总是有一个名为“dummy”的列,其中包含“X”。因此,以下语句永远不会返回行。
select * from dual where dummy='123';
因此,通过nvl函数,该语句将始终返回“NO RECORD FOUND”
select nvl((select * from dual where dummy='123'),'NO RECORD FOUND') value from dual;
...但是,如果您真的想要NULL,可以执行以下操作(如上所述)
select (select * from dual where dummy='123') value from dual;
当然,将上述选择语句与您自己的语句交换
COALESCE((SELECT idnumber FROM dbo.database WHERE number = '9823474'),0)
将0
替换为任何您想要返回的值,如果选择查询不返回任何行。
COALESCE
捕获null
值,但无法捕获“无行”。 - Erwin Brandstetter