可在 SQL Server 和 MS Access 中使用的 COALESCE、IFNULL 或 NZ() 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7910794/
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
COALESCE, IFNULL, or NZ() function that can be used in SQL Server and MS Access
提问by Larry Lustig
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:
我有一个可以使用 SQL Server 或 MS Access 作为数据存储的项目。在一个 SELECT 语句中，我必须对单个列和单个值执行 COALESCE 操作，如下所示：
SELECT COALESCE([Amount], 0) FROM PaymentsDue;
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.
我想编写一个可以在 SQL Server 和 MS Access 中正确执行的 SQL 语句。最受关注的 SQL Server 版本是 2008，但首选适用于跨版本的解决方案。
Earlier today, someone was able to show me an SQL trickthat 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 bothSQL Server and MS Access?
今天早些时候，有人向我展示了一个 SQL 技巧，它允许我使用单个 SELECT 语句有效地将 DATETIME 转换为 DATE。我不知道是否有人也有类似的伎俩来执行在可应用于方式COALESCE（如，IFNULL或NZ）操作都SQL Server和MS访问？
采纳答案by Stuart Ainsworth
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.
显然，如果您有多个列，这将很快变得难以管理。
回答by onedaywhen
I don't think there is any syntax that functions the same on both platforms.
我认为没有任何语法在两个平台上的功能相同。
Note Nz()is only available when using the Access user interface.
注意Nz()仅在使用 Access 用户界面时可用。
Here are a couple of suggestions that can be transformed to COALESCEfairly easily, though repeating the column is a pain:
以下是一些可以COALESCE相当容易地转换为的建议，尽管重复该列很痛苦：
Sample 1:
示例 1：
SELECT IIF([Amount] IS NULL, 0, [Amount]) FROM PaymentsDue;
Sample 2:
示例 2：
SELECT SWITCH([Amount] IS NULL, 0, TRUE, [Amount]) FROM PaymentsDue;
回答by Homer T. Nacho Cheese
Create a custom public function in a module.
在模块中创建自定义公共函数。
Public Function COALESCE(InputValue, ValueIfNull)
COALESCE = nz(InputValue, ValueIfNull)
End Function
Add in error handling, etc., make improvements.
添加错误处理等，进行改进。
Now, you would be able to use the COALESCEfunction in MS Access and SQL.
现在，您将能够COALESCE在 MS Access 和 SQL 中使用该功能。
回答by Philippe Grondier
And I guess you do not want to write a parser that will manage translations between Jet SQL and T-SQL ...
而且我猜您不想编写一个解析器来管理 Jet SQL 和 T-SQL 之间的翻译......
A solution that we developped (yes, we had a similar problem to solve) is to define some 'pseudo-metalanguage' that we use in our meta-SQL syntax, and we have a kind of translator from this meta-language into Jet SQL or T-SQL.
我们开发的一个解决方案（是的，我们有一个类似的问题要解决）是定义一些我们在元 SQL 语法中使用的“伪元语言”，我们有一种从这种元语言到 Jet SQL 的翻译器或 T-SQL。
Example:
例子：
myQuery = "SELECT @MyCoalesceFunction@([Amount], 0) FROM PaymentsDue;"
myQuery = convertFromMeta(myQuery,"T-SQL")
will give
"SELECT COALESCE([Amount], 0) FROM PaymentsDue;"
myQuery = convertFromMeta(myQuery,"JET-SQL")
will give
"SELECT NZ([Amount], 0) FROM PaymentsDue;"
The same strategy could be used for wildcards and delimiters:
相同的策略可用于通配符和分隔符：
myQuery = "SELECT [Amount] FROM PaymentsDue WHERE id_client LIKE @CarSep@ABC@MyWildCard@@CarSep@"
myQuery = convertFromMeta(myQuery,"T-SQL")
will give
"SELECT [Amount] FROM PaymentsDue WHERE id_client LIKE 'ABC%'"
myQuery = convertFromMeta(myQuery,"JET-SQL")
will give
"SELECT [Amount] FROM PaymentsDue WHERE id_client LIKE "ABC%""
I konw it's not that nice, but it is quite efficient and clean. The main points are:
我知道它不是那么好，但它非常高效和干净。要点是：
- We are not translating between Jet and T-SQL, but from a 'meta-syntax'. It makes things a lot easier
- One should be very careful when functions do not have the same number of parameters, or when parameters are not passed in the same order. It still can be done ...
- Our meta-syntax relies on the fact that the corresponding strings (like '@MyWildCard@' or '@CarSep@') are specific to our syntax, and cannot be used as data values (otherwise we would have to manage some 'meta-injection' risks!...)
- 我们不是在 Jet 和 T-SQL 之间进行翻译，而是从“元语法”进行翻译。它让事情变得容易多了
- 当函数没有相同数量的参数，或者参数的传递顺序不同时，应该非常小心。还是可以做到的...
- 我们的元语法依赖于这样一个事实，即相应的字符串（如“@MyWildCard@”或“@CarSep@”）特定于我们的语法，并且不能用作数据值（否则我们将不得不管理一些“元”注射的风险！...）
