使用嵌套的IIF优化访问查询 [英] Optimize access query with nested IIF's

问题描述

在我的where子句中有更好的方法编写以下内容吗?

Is there a better way to write the following in my where clause?

WHERE (IIf([GrpOrder]=3,IIf([LabelText]="Totals",True,False),True)) =True))

谢谢

斯科特

推荐答案

我假设您的代码包含错别字(无括号的括号)，实际上应该读为:

I assume your code contains typos (unblanaced parentheses) and should in fact read:

WHERE IIf([GrpOrder]=3,IIf([LabelText]="Totals",True,False),True) = true

从SQL代码的角度来看，由于SQL具有NULL的三值逻辑，实际上需要考虑九种情况:

From a SQL code perspective there are actually nine cases to consider because of SQL's three value logic with NULL:

GrpOrder = 3
GrpOrder <> 3
GrpOrder IS NULL

LabelText = 'Totals'
LabelText <> 'Totals'
LabelText IS NULL

总共有9种情况，例如测试数据和结果:

In combination there are nine cases e.g. test data and results:

OP_result | GrpOrder | LabelText  
----------------------------------
     TRUE |       55 | 'Totals'
     TRUE |       55 | 'Tallies'
     TRUE |       55 | <NULL>
     TRUE |        3 | 'Totals'
    FALSE |        3 | 'Tallies'
    FALSE |        3 | <NULL>
     TRUE |   <NULL> | 'Totals'
     TRUE |   <NULL> | 'Tallies'
     TRUE |   <NULL> | <NULL>

最安全的方法是写出一系列OR子句，分别为每个OR子句的两列处理NULL.但是，这很漫长，最好标记这两个返回FALSE的情况.这就是大多数人(包括我！)遇到NULL问题的地方:这太违反直觉了！

The safest approach would be to write out a series of OR clauses, explcitly handling NULL for both column for each OR clause. However, that is very long winded it would be better to taget those two cases that return FALSE. And this is where most folk (including me!) run into problems with NULL: it's just too counter-intuitive!

例如，写这个很诱人:

(GrpOrder = 3 AND LabelText IS NULL)
OR
(GrpOrder = 3 AND LabelText <> 'Totals')

然后使用NOT翻转"其值:

NOT (
     (GrpOrder = 3 AND LabelText IS NULL)
     OR
     (GrpOrder = 3 AND LabelText <> 'Totals')
    )

但是，这样做NULL会潜入结果集中:

However, in doing so NULL sneaks into the resultset:

OP_result | attempt_1 | GrpOrder | LabelText  
---------------------------------------------
     TRUE |      TRUE |       55 | 'Totals'
     TRUE |      TRUE |       55 | 'Tallies'
     TRUE |      TRUE |       55 | <NULL>
     TRUE |      TRUE |        3 | 'Totals'
    FALSE |     FALSE |        3 | 'Tallies'
    FALSE |     FALSE |        3 | <NULL>
     TRUE |      TRUE |   <NULL> | 'Totals'
     TRUE |    <NULL> |   <NULL> | 'Tallies'
     TRUE |    <NULL> |   <NULL> | <NULL>

因此，我们需要显式处理比乍看之下要多的案例.

So we need to explicitly handle more cases than it might appear at first glance.

我能想到的最简单的谓词可以在Access中获得预期的结果:

The simplest predicate I could come up with that gives the desired result in Access:

NOT
(
 (LabelText <> 'Totals' OR LabelText IS NULL)
 AND GrpOrder = 3 
 AND GrpOrder IS NOT NULL
)

[...阅读起来很奇怪，我想知道OP的代码是否首先产生了期望的结果.]

[...which is so odd to read I wonder whether the OP's code is yielding the desired result in the first place.]

要学习的主要课程:

• 应避免在SQL中使用NULL:即使是非常有经验的SQL编码人员，这也违反直觉，甚至会导致错误.
• 始终以预期结果发布架构(例如CREATE TABLE SQL DDL ...)和示例数据(...例如INSERT INTO SQL DML ...)(如果需要，请使用文字和图片) ;)因为如果您的列被标记为NOT NULL，则答案非常简单！ :)

• NULL in SQL should be avoided: it is counter-intuitive even causes bugs even by very experienced SQL coders.
• Always post your schema (e.g. CREATE TABLE SQL DDL...) and sample data (... e.g. INSERT INTO SQL DML...) with expected results (... or use words and pictures if you must ;) because if your columns are marked as NOT NULL then the answer is very much simpler! :)

@Yanir Kleiman评论:

@Yanir Kleiman comments:

GrpOrder不能为3并且在NULL 同时，因此检查它是否不为null 在这种情况下是多余的

GrpOrder can't be 3 and NULL at the same time, so checking it is not null in this case is redundant

可以这样认为是可以原谅的.但这就是Access :)我们拥有声称符合SQL标准的SQL产品的出色规范. Access声称没有此类合规性，并且

One could be forgiven for thinking so. But this is Access :) We have excellent specs for SQL products that claim compliance with the SQL Standards. Access claims no such compliance and the documentation the Access Team have provided is of a particularly low quality.

相反，在Access-land中，要使某些事情真实，您必须进行实际测试！

Rather, in Access-land, for something to be true, you have to actually test it!

当我删除谓词

AND GrpOrder IS NOT NULL

空出现在结果集中.尽管感觉像违背逻辑"，但请记住，SQL的三值逻辑仅在Access声明不符合的规范中定义.如果访问团队没有告诉我们该产品应该如何工作，那么我们如何确定以上内容是错误还是功能?即使我们可以说服他们这是一个错误，他们也会解决吗?

nulls appear in the resultset. While it feels like this 'defies logic', bear in mind that SQL's three value logic is only defined in a spec to which Access claims no compliance. If the Access Team don't tell us how the product is supposed to work, how can we tell whether the above is a bug or a feature? And even if we could convince them it is a bug, would they fix it?

下面，我提供VBA代码来重现此问题:只需将+复制粘贴到任何VBA模块中，无需设置引用.它在temp文件夹中创建一个新的.mdb，然后创建表和测试数据.无需在机器上安装访问权限，例如使用Excel的VBA编辑器.

Below I provide VBA code to reproduce the issue: just copy+paste into any VBA module, no references need to be set. It creates a new .mdb in the temp folder, then creates the table and test data. Access need not be installed on the machine e.g. use Excel's VBA editor.

分别包含和删除上述谓词时，消息框显示显示结果集.除了两个表列之外，还有两个计算列显示值-1(TRUE)，0(FALSE)和NULL，最左边的是OP:

The messagebox shows shows the resultset when the above predicate is included and removed respectively. In addition to the two table columns, two calculated columns show with values -1 (TRUE), 0 (FALSE) and NULL and the leftmost one is the OP's:

Sub AccessStrangeLogic()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim Sql As String
      Sql = _
      "CREATE TABLE GrpOrders" & vbCr & _
      "(" & vbCr & _
      " GrpOrder INTEGER," & vbCr & _
      " LabelText NVARCHAR(10)" & vbCr & _
      ");"
      .Execute Sql

      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (55, 'Totals');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (55, 'Tallies');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (55, NULL);"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (3, 'Totals');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (3, 'Tallies');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (3, NULL);"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (NULL, 'Totals');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (NULL, 'Tallies');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (NULL, NULL);"
      .Execute Sql

      ' Include "AND GrpOrder IS NOT NULL"
      Sql = _
      "SELECT *, " & vbCr & _
      "       IIf([GrpOrder]=3,IIf([LabelText]=""Totals"",True,False),True) = true AS OP_result, " & vbCr & _
      "       NOT" & vbCr & _
      "       (" & vbCr & _
      "        (LabelText <> 'Totals' OR LabelText IS NULL)" & vbCr & _
      "        AND GrpOrder = 3 " & vbCr & _
      "        AND GrpOrder IS NOT NULL" & vbCr & "       )" & vbCr & _
      "  FROM GrpOrders" & vbCr & _
      " ORDER " & vbCr & _
      "    BY GrpOrder DESC, LabelText DESC;"

      Dim rs
      Set rs = .Execute(Sql)

      ' Remove "AND GrpOrder IS NOT NULL"
      Sql = Replace$(Sql, "AND GrpOrder IS NOT NULL", "")

      Dim rs2
      Set rs2 = .Execute(Sql)

      MsgBox _
          "Include 'AND GrpOrder IS NOT NULL':" & vbCr & _
          rs.GetString(, , vbTab, vbCr, "<NULL>") & vbCr & _
          "remove 'AND GrpOrder IS NOT NULL':" & vbCr & _
          rs2.GetString(, , vbTab, vbCr, "<NULL>")


    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

这篇关于使用嵌套的IIF优化访问查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！