Access SQL 中的合并替代方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/247858/
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 alternative in Access SQL
提问by Nathan DeWitt
In T-SQL, you can do this:
在 T-SQL 中，您可以这样做：
SELECT ProductId, COALESCE(Price, 0)
FROM Products
How do you do the same thing in Access SQL? I see examples for doing it with Nz in VBA, but I'm looking for the SQL equivalent.
你如何在 Access SQL 中做同样的事情？我在 VBA 中看到了用 Nz 做这件事的例子，但我正在寻找 SQL 等价物。
Thanks.
谢谢。
采纳答案by pipTheGeek
Access supports the Nz function and allows you to use it in a query. Note though that Nz is the same as the T-SQL ISNULL function. It can not take an arbitary number of parameters like COALESCE can.
Access 支持 Nz 函数并允许您在查询中使用它。请注意，Nz 与 T-SQL ISNULL 函数相同。它不能像 COALESCE 那样采用任意数量的参数。
回答by Codewerks
If it's in an Access query, you can try this:
如果它在 Access 查询中，你可以试试这个：
"Price = IIf([Price] Is Null,0,[Price])"
回答by Nathan DeWitt
Looks like I can just use:
看起来我只能使用：
SELECT ProductId, Nz(Price, 0)
FROM Products
Seems to be working just fine.
似乎工作得很好。
回答by Patrick Honorez
Using Iif(Price is null, 0, Price)should give you the best performance (see Allen Browne's performance tips). However SQL Server Coalesce()has the great advantage over Iif()and Nz()that it can handle several parameters in a cascade. So I created this quick VBA equivalent:
使用Iif(Price is null, 0, Price)应该会给你最好的性能（参见Allen Browne 的性能提示）。但是SQL服务器Coalesce()具有很大的优势Iif()和Nz()它可以在级联处理几个参数。所以我创建了这个快速的 VBA 等效项：
Function Coalesce(ParamArray varValues()) As Variant
'returns the first non null value, similar to SQL Server Coalesce() function
'Patrick Honorez --- www.idevlop.com
Dim i As Long
Coalesce = Null
For i = LBound(varValues) To UBound(varValues)
If Not IsNull(varValues(i)) Then
Coalesce = varValues(i)
Exit Function
End If
Next
End Function
回答by Art Mendenhall
Using IsNull(), Nz(), and the data conversion functions are built-in VBA functions and will only slow down your queries in versions prior to 2003. As far as datatyping goes use CCur()to guarantee your data type, but only if you need to do strong comparisons or simply set the format property to Currency on the column. It is the IF statement that slows things the most, as it adds yet another function to your routine
使用IsNull()、Nz()和 数据转换函数是内置的 VBA 函数，只会减慢 2003 之前版本的查询速度。至于数据类型CCur()用于保证您的数据类型，但前提是您需要进行强比较或简单地将列上的格式属性设置为货币。IF 语句最慢，因为它为您的例程添加了另一个功能
using this solution: Nz([Price], CCur(0))
使用此解决方案： Nz([Price], CCur(0))
the only time CCur()will execute is when Price Is Null, so overall this is probably the fastest.
唯一CCur()会执行的时间是价格为空时，所以总的来说这可能是最快的。
The point is that the least number of total functions used, the faster your queries will execute.
关键是使用的总函数数越少，您的查询执行得越快。
回答by user4406949
COALESCE or NULLIF function are the standard used on sql server for a good migration to access. ISNULLor IIF or CHOOSE are nonstandard function.
COALESCE 或 NULLIF 函数是 sql server 上用于良好迁移到访问的标准。ISNULL 或 IIF 或 CHOOSE 是非标准函数。
