在 Excel VBA 中使用 SQL“分组依据”函数对数据进行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22759695/
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
Group Data using SQL "Group By" function in Excel VBA
提问by user3480701
I have two tables (1&2) that should be merged into a third (3) one.
我有两个表 (1&2) 应该合并到第三个 (3) 中。
Table1: F_Number, A_Number, A_Weight
Table2: A_Number, A_Country
Table3: F_Number, A_Country, A_Weight
表1：F_Number、A_Number、
A_Weight
表2：A_Number、A_Country表3：F_Number、A_Country、A_Weight
The thrid table should be grouped by F_Number summing up A_Weight where A_Country has the same value. The joining of the tables works fine so far:
第三个表应按 F_Number 和 A_Weight 进行分组，其中 A_Country 具有相同的值。到目前为止，表的连接工作正常：
Dim strSQL As String
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String
Dim sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
Conn.Open sconnect
strSQL = "Select [Table1$].[F_Number], [Table2$].[A_Country], [Table1$].[A_Weight] From [Table1$] Inner Join [Table2$] On [Table2$].[A_Number]=[Table1$].[A_Number]"
mrs.Open strSQL, Conn
ActiveSheet.Range("A2").CopyFromRecordset mrs
mrs.Close
Conn.Close
The Result is a table like this:
结果是这样的表：
F_Number; A_country; A_Weight
F_1; US; 10%
F_1: US; 10%
F_1; UK; 80%
F_2; UK; 100%
F_号码；一个国家; A_权重
F_1；我们; 10%
F_1：美国；10%
F_1；英国; 80%
F_2；英国; 100%
I want this table to be grouped by "A_Weight" like this:
我希望这张表按“A_Weight”分组，如下所示：
F_Number; A_country; A_Weight
F_1; US; 20%
F_1; UK; 80%
F_2; UK; 100%
F_编号；一个国家; A_权重
F_1；我们; 20%
F_1；英国; 80%
F_2；英国; 100%
I do not know much about SQL, but what I tried so far is to sum up A_Weight and Group the result by F_Number, which gives me an error message. I am using a german office version, thus I can only try to translate the error: "Automatization Error". I am still thinking it should work something like this.
我对 SQL 不太了解，但到目前为止我尝试的是总结 A_Weight 并按 F_Number 将结果分组，这给了我一个错误消息。我使用的是德国办公室版本，因此我只能尝试翻译错误：“自动化错误”。我仍然认为它应该像这样工作。
Here is my non-workingcode for the SQL-String:
这是我的 SQL-String 的非工作代码：
strSQL = "Select [Table1$].[F_Number], [Table2$].[A_Country], Sum([Table1$].[A_Weight]) From [Table1$] Inner Join [Table2$] On [Table2$].[A_Number]=[Table1$].[A_Number] Group By [Table1$].[F_Number]"
采纳答案by Pankaj Jaju
Your code is fine; you just need to modify the SQL query.
你的代码很好；您只需要修改 SQL 查询。
strSQL = "SELECT [Table1$].[F_Number], [Table2$].[A_Country], SUM([Table1$].[A_Weight]) " & _
"FROM [Table1$], [Table2$] " & _
"WHERE [Table2$].[A_Number] = [Table1$].[A_Number] " & _
"GROUP BY [Table1$].[F_Number], [Table2$].[A_Country]"
