如何使用SQL连接表，在Access-VBA函数中选择最大值？

小编典典

如何使用SQL连接表，在Access-VBA函数中选择最大值？

sql

我目前具有以下Access VBA功能，该功能按上一个问题中的说明进行操作（对于理解该问题非常有用）：

Private Function MapFields(tableNameTemp As String, tableName As String, commonField As String, newTableName)

    Dim tableNameFieldCount As Integer
    tableNameFieldCount = GetFieldCount(tableName)
    Dim tableNameFieldsArray() As String
    ReDim tableNameFieldsArray(0 To tableNameFieldCount) As String ' since you cannot Dim against a variable size
    Call GetFields(tableName, tableNameFieldsArray)    

    sqlJoinQuery = "SELECT tbl_grp_by.*, [" + tableName + "].* " & _
    "INTO " + newTableName & _
    " FROM (SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1], " & _
    "Max([" + tableNameTemp + "].[Field2]) as [Field2], " & _
    "Max([" + tableNameTemp + "].[Field3]) as [Field3], " & _
    "[" + tableNameTemp + "].[Field4] as [Field4] " & _
    "FROM [" + tableNameTemp & _
    "] INNER JOIN [" + tableName & _
    "] ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
    "GROUP BY [" + tableNameTemp + "].[" + commonField + "]) as tbl_grp_by " & _
    "INNER JOIN [" + tableName & _
    "] ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"


    Debug.Print sqlJoinQuery
    CurrentDb.Execute sqlJoinQuery

End Function

但是，Field3可以在我的数据中包含几个值之一，而不是包含yes或no。为简单起见，假设这些值可以从下面的一组字符串的任何一个项目：

（0，>1 million，0001-0010）

在这种情况下，Max()Access SQL函数将不起作用，Field3因为它们是具有用户定义层次结构的字符串。但是，我需要选择最大的价值。我将定义从最低（1）到最高（3）的值：

0
1 million
0001-0010

这是tableNameTemp可能如何显示的示例：

╔════════════════════════╦════════╦════════╦══════════════════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3               ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  SA12                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  Yes   ║  No    ║  0001-0010           ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  No    ║  >1 million          ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  Yes   ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  Yes   ║  No    ║  >1 million          ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  No    ║  No    ║  >1 million          ║
╚════════════════════════╩════════╩════════╩══════════════════════╝

并以上面的示例tableNameTemp值为例，下表显示了如何将这些值映射到tableName表上。请注意，它如何选择要映射的最大值。

╔════════════════════════╦════════╦════════╦══════════════════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3               ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  SA12                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  Yes   ║  Yes   ║  0001-0010           ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  Yes   ║  No    ║  >1 million          ║
╚════════════════════════╩════════╩════════╩══════════════════════╝

由于我既没有Access SQL方面的经验，也没有VBA的丰富经验，所以我对如何实现此目标感到困惑。我猜想这可能涉及对的可能值列表进行硬编码Field3，然后根据其在列表中的位置对它们进行排名（即位置3将包含该值0）。然后，以某种方式将其用作选择哪个值的键。如果有什么用，我在下面提供了代码：

Dim hierarchy(0 to 2) As String
hierarchy(0) = "0001-0010" ' highest value '
hierarchy(1) = ">1 million"
hierarchy(2) = "0"         ' lowest value  '

编辑
更新的代码，根据Serg的帮助：

sqlJoinQuery = "SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2], " & _
                        "Switch( " & _
                            "tbl_grp_by.[maxfield3] = 0, '0', " & _
                            "tbl_grp_by.[maxfield3] = 1, '>1 million', " & _
                            "tbl_grp_by.[maxfield3] = 2 '0001-0010' " & _
                        ") as [Field3], " & _
                        "tbl_grp_by.[Field4], " & _
                    "[" + tableName + "].* " & _
                    "INTO [" + newTableName + "] FROM (" & _
                        "SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1], " & _
                            "Max([" + tableNameTemp + "].[Field2]) as [Field2], " & _
                            "Max(Switch( " * _
                                "[" + tableNameTemp + "].[Field3] = '0' , 0, " & _
                                "[" + tableNameTemp + "].[Field3] = '>1 million' , 1, " & _
                                "[" + tableNameTemp + "].[Field3] = '0001-0010', 2 " & _
                            "))as [maxField3], " * _
                            "[" + tableNameTemp + "].[Field4] as [Field4] " * _
                        "FROM [" + tableNameTemp + "] " & _
                        "INNER JOIN [" + tableName + "] " & _
                            "ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
                        "GROUP BY [" + tableNameTemp + "].[" + commonField + "] " & _
                    ") as tbl_grp_by " & _
                    "INNER JOIN [" + tableName + "] " & _
                        "ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"

阅读 447

2021-05-23

共1个答案

小编典典

编码值，在SQL中以这种方式最大解码。

当前，您正在将SQL命令构建为（我用任意值替换了表名变量， temp并使用tableName）

SELECT tbl_grp_by.*, [tableName].*  
INTO newTableName 
FROM (
    SELECT Max([temp].[Field1]) as [Field1], 
        Max([temp].[Field2]) as [Field2],  
        Max([temp].[Field3]) as [maxField3], 
        [temp].[Field4] as [Field4]  
    FROM [temp]
    INNER JOIN [tableName ]
       ON [temp].[commonField] = [tableName].[commonField] 
    GROUP BY [temp].[commonField]
 ) as tbl_grp_by  
INNER JOIN [tableName]
  ON [tableName].[commonField] = tbl_grp_by.[commonField]

建立为

SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2],
    Switch( 
        tbl_grp_by.[maxfield3] = 0, '0',
        tbl_grp_by.[maxfield3] = 1, '>1 million',
        tbl_grp_by.[maxfield3] = 2 '0001-0010' 
    ) as [Field3],   
    tbl_grp_by.[Field4],
[tableName].*  
INTO newTableName 
FROM (
    SELECT Max([temp].[Field1]) as [Field1], 
        Max([temp].[Field2]) as [Field2],  
        Max(Switch(  
            [temp].[field3] = '0' , 0,
            [temp].[field3] = '>1 million' , 1,
            [temp].[field3] = '0001-0010', 2  
         ))as [maxField3], 
        [temp].[Field4] as [Field4]  
    FROM [temp]
    INNER JOIN [tableName ]
       ON [temp].[commonField] = [tableName].[commonField] 
    GROUP BY [temp].[commonField]
 ) as tbl_grp_by  
INNER JOIN [tableName]
  ON [tableName].[commonField] = tbl_grp_by.[commonField]

因此[field3]在内部查询中的max（）下编码，而在外部查询中的max解码。

2021-05-23