使用`SELECT *`时计算列的位置错误

问题描述

考虑以下使用SELECT *并附加"计算列的查询:

Consider this query that uses SELECT * and 'appends' a calculated column:

SELECT *,
       IIF(TRUE, 1, 0) AS calculated_col
  FROM Orders;

我希望calculated_col是结果集中最右边的列.但是，实际上它是最左侧的列.例如，在SQL Server中执行等效查询时，它是最右边的.

I would expect calculated_col to be the rightmost column in the resultset. However, it is in fact the leftmost column. It is the rightmost when executing the equivalent query in SQL Server, for example.

现在，由于这是Access(ACE，Jet等)，因此SQL标准不适用，并且Access Help将不会指定期望的结果，因为它不够详细(礼貌地说).所以我的问题是:

Now, because this is Access (ACE, Jet, whatever), the SQL Standards don't apply and the Access Help will not specify the expected result because it is not detailed enough (to put it politely). So my questions are:

Access总是这样运行还是它是我的环境(ADO，OLE DB提供程序等)的功能"?

Does Access always behaved this way or is it a 'feature' of my environment (ADO, OLE DB provider, etc)?

在给定的环境中，Access总是表现出这种方式吗? (即为什么我以前没有注意到这一点?)

Has Access always behaved this way in the given environment? (i.e. Why haven't I noticed this before?)

P.S.我当然知道SELECT *被广泛嘲笑，并且如果列的顺序对我很重要，那么我应该明确地将它们全部写出.但是，我真的为所遇到的实际行为感到惊讶，并且对我的问题的解答很感兴趣.

P.S. I know of course that SELECT * is widely derided and that if the order of columns is important to me then I should write them all out explicitly. However, I was genuinely suprised at the actual behaviour encountered and am interested in any answers to my questions.

这里有一些VBA可重现此行为:只需将+复制粘贴到任何VBA模块中，无需设置引用，也不需要安装Access.使用Excel的VBA编辑器:

Here's some VBA to reproduce the behaviour: just copy+paste into any VBA module, no references need to be set and Access need not be installed e.g. use Excel's VBA editor:

Sub ColumnOrderWrong()

  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 Orders" & vbCr & _
      "(" & vbCr & " ID INTEGER, " & vbCr & _
      " customer_id" & _
      " INTEGER" & vbCr & _
      ");"
      .Execute Sql

      Sql = _
      "INSERT INTO Orders (ID, customer_id) VALUES" & _
      " (1, 2);"
      .Execute Sql

      Sql = _
      "SELECT *, " & vbCr & _
      "       IIF(TRUE, 55, -99) AS calculated_col" & vbCr & _
      "  FROM Orders;"
      Dim rs
      Set rs = .Execute(Sql)

      MsgBox _
      "Fields(0).Name = " & rs.Fields(0).Name & vbCr & _
      "Fields(1).Name = " & rs.Fields(1).Name & vbCr & _
      "Fields(2).Name = " & rs.Fields(2).Name

    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

推荐答案

从您已经完成的调查看来，您好像被塞满了.

From the investigations you've already done, it looks like you're stuffed.

您可能必须按名称而不是位置来引用记录集的列.

You may have to reference the record set's column's by name rather than position.

这篇关于使用`SELECT *`时计算列的位置错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！