可在SQL Server和MS Access中使用的COALESCE,IFNULL或NZ()函数 [英] COALESCE, IFNULL, or NZ() function that can be used in SQL Server and MS Access
问题描述
我有一个可以使用SQL Server或MS Access作为数据存储的项目.在一个SELECT语句中,我必须对单个列和单个值执行COALESCE操作,如下所示:
I have a project that can use either SQL Server or MS Access as the data store. In one SELECT statement, I must perform a COALESCE operation on a single column and a single value, like this:
SELECT COALESCE([Amount], 0) FROM PaymentsDue;
我想编写一条将在SQL Server和MS Access中正确执行的SQL语句.最受关注的SQL Server版本是2008,尽管首选适用于各个版本的解决方案.
I would like to write a single SQL statement that will execute correctly in both SQL Server and MS Access. The SQL Server version that is of immediate interest is 2008, although a solution applicable across versions would be preferred.
Earlier today, someone was able to show me an SQL trick that allowed me to use a single SELECT statement to effectively CAST a DATETIME to DATE. I was wondering if anyone has a similar trick to perform a COALESCE (eg, IFNULL or NZ) operation in a way that can be applied to both SQL Server and MS Access?
推荐答案
这可以工作,但是很笨拙:
This will work, but it's clunky:
SELECT Amount
FROM PaymentsDue
WHERE Amount IS NOT NULL
UNION ALL
SELECT 0 AS Amount
FROM PaymentsDue
WHERE Amount IS NULL
很显然,如果您有多于一列,那么很快就会变得难以管理.
Obviously if you have more than one column, this gets to be quickly unmanageable.
这篇关于可在SQL Server和MS Access中使用的COALESCE,IFNULL或NZ()函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!