Access SQL中的coalesce替代方法

Question

T-SQL 中，您可以这样做：

SELECT ProductId, COALESCE(Price, 0)
FROM Products

如何在Access SQL中执行相同的操作？我看到使用VBA中的Nz进行操作的示例，但我正在寻找SQL等效方法。

谢谢。

- Nathan DeWitt

Answer 1

如果是在 Access 查询中，您可以尝试这样做：

"Price = IIf([Price] Is Null,0,[Price])"

- AugustLights

---

为什么不用 IIf(IsNull(Price), 0, Price) ？ - David-W-Fenton

你会如何在查询中使用这个？ - Jon

因为IsNull()是VBA语言并会导致程序速度变慢，而Is Null可以被Jet等数据库原生处理。请查看我回答中提供的链接，阅读Allen Browne的页面了解更多信息。 - iDevlop

为了方便未来的读者理解，IsNull 是 MS Access SQL 的一部分（不仅仅是 VBA）。与 NZ 不同，您可以在后端 ODBC/OLEDB 查询中使用此函数。 - Parfait

Answer 2

Access支持Nz函数，并允许您在查询中使用它。请注意，Nz与T-SQL的ISNULL函数相同。不过，它无法像COALESCE那样接受任意数量的参数。

- pipTheGeek

---

“Jet（Access数据库引擎）SQL也支持Nz函数”- 不正确，Jet没有NZ()函数。 MS Access对象模型具有可以在查询中仅在MS Access界面内使用的NZ()函数。 在MS Access界面外使用NZ()会导致错误。 - onedaywhen

由于某种原因，NZ([Field], 0) 改变了字段的类型（在结果集中左对齐），而 IIF ([Price] is null, 0, [Price]) 却运行良好。 - Gabriel GM

FYI：新的ACE数据库引擎（取代旧的Jet引擎）本身支持一些VBA函数，包括Nz()。我认为这对应于Access 2010及更高版本。这主要是必要的，因为ACE引擎支持数据宏（类似于其他SQL DBMS中的触发器），这些宏直接支持，即使在Access环境之外也是如此。好处是这样的函数也可以在SQL查询中使用。 - C Perkins

@CPerkins，我使用pyODBC的ACE 2010版本，但是出现了“[Microsoft][ODBC Microsoft Access Driver] Undefined function 'NZ' in expression”错误。 - Cristian Ciupitu

我验证了在各种ODBC客户端中都不允许使用Nz()。但我也验证了在其他上下文中，ACE引擎能够识别Nz()。需要注意的是，在使用ODBC调用DatePart()和Now()等函数时，我会收到各种错误消息，但仍可以获得有效数据。但是，如果我尝试执行组合调用，ODBC就会抱怨并且不允许它，即使它是完全正确的SQL格式并且这些函数是允许单独使用的。总之，Nz()确实不受支持，但只是因为ODBC根据自己的规则不允许将其传递给引擎。 - C Perkins

Answer 3

看起来我可以直接使用：

SELECT ProductId, Nz(Price, 0)
FROM Products

看起来工作正常。

- Nathan DeWitt

---

你需要注意参数的结果数据类型，因为当你期望它是数字类型时，并不总是这样。虽然理论上来说，它应该会选择第一个参数的数据类型，但我从未完全弄清楚其规律。 - David-W-Fenton

此外，在查询中使用VBA函数要小心，它们可能会导致性能问题。 - JohnFx

Answer 4

使用Iif(Price is null, 0, Price)应该能够提供最佳性能（详见Allen Browne的性能技巧）。然而，SQL Server 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

- iDevlop

Answer 5

使用 IsNull()、Nz() 和数据转换函数是内置的 VBA 函数，仅在 2003 版本之前的查询中降低查询速度。关于数据类型，请使用 CCur() 来保证数据类型，但仅在需要进行强比较或仅在列上设置格式属性为货币时使用。最慢的是 IF 语句，因为它会向您的例程添加另一个函数。

使用这个解决方案: Nz([Price], CCur(0))

只有当 Price 为空时，CCur() 才会执行，因此总体来说，这可能是最快的。

重点是使用的函数总数越少，查询执行速度就越快。

- Art Mendenhall

Answer 6

很遗憾，我的stackoverflow账户使用的是旧的电子邮件地址，因此我没有足够的信誉来对某些回复进行投票或单独回复。

感谢您的帖子。我添加了一个公共函数，返回类型为Double，并使用0.00作为默认值。我调用了@iDevlop的Coalesce函数，使查询结果具有类型。Nz()函数将返回0.00的默认值为0，我必须将其乘以1才能使其成为数字，然后Access将使用其格式。

习惯于sql-server，我错过了Coalesce函数，这真的是一个时间节省器。当它不起作用或者我需要在查询中选择多个值时，我到处使用IIf和Nz。

因此，感谢本页的贡献者。

' Use Coalesce instead of Nz and return a double so that it displays correctly.

' If you pass back a 0, it will not be displayed a 0.00 unless you cast it as a fixed(2), or a double, or multiply a Nz returned result times a number.
Public Function CoalesceDbl(dbl As Variant) As Double
    CoalesceDbl = Coalesce(dbl, 0#)
End Function

- Robert Gray

Answer 7

这是一个非常古老的帖子，但我很感兴趣看看是否有人有COALESCE的多个参数的替代方案。至于上面的答案，您可以使用像这样可怕的hacky版本：

SELECT 
    CCur("0" & Price)
...

这个方法的原理是因为连接符（&）在将一个值附加到另一个值时，会强制将NULL转换为空字符串。

当你需要将名字和称号等信息拼接在一起时，如果其中包含NULL字段，这种方法可以发挥很大的作用。例如：

SELECT 
    [SURNAME] & ", " + [Forename] & " (" + [Title] + ")"

/*  └───────┘   └───────────────┘   └──────────────────┘
     Part 1      Part 2              Part 3  */
...

这个例子将所有结果连接成一个变体字符串。然后我们尝试向[FORENAME]字段添加（而不是连接）", "。如果这个字段是NULL，那么添加到NULL的任何内容都是NULL。然后我们尝试将"("和")"添加到[TITLE]字段的结果进行连接。

首先计算部分1、2和3，然后将它们连接在一起，最多会产生一个空字符串（而不是NULL值）。这样做是因为连接的优先级低于加法。

我曾经经常使用这个和转换方法，但几年前。请注意，正如上文中@pipthegeek发表的评论所强调的，利用VBA在查询中可能会使它慢得多。

- user1945782

Answer 8

COALESCE或NULLIF函数是在SQL Server中用于良好迁移到Access的标准。ISNULL或IIF或CHOOSE是非标准函数。

- user4406949