SQL 在 Access 中连接记录和 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15624845/
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
Concatenate records and GROUP BY in Access
提问by JBurace
I have a table like this:
我有一张这样的表：
title part desc
Blah This 1 This begins the
Blah This 2 example table.
Some Record 1 Hello
Another 1 This text extends a bit
Another 2 further so it is in
Another 3 another record in the
Another 4 table
In Access, I am looking to build a query/SQL to GROUP BY titleand concatenate the descfield so it looks like this:
在 Access 中，我希望构建一个查询/SQL 到 GROUP BYtitle并连接desc字段，因此它看起来像这样：
title desc
Blah This This begins the example table.
Some Record Hello
Another This text extends a bit further so it is in another record in the table
How can this be done with just SQL (no VBA/scripts)? FOR XML PATHdoes not seem to work in Access, only SQL Server. I've tried VBA here How to improve efficiency of this query & VBA?, but it is simply too slow.
仅使用 SQL（无 VBA/脚本）如何做到这一点？FOR XML PATH似乎在 Access 中不起作用，只能在 SQL Server 中运行。我在这里尝试过 VBA如何提高此查询和 VBA 的效率？，但它实在是太慢了。
Or is there a function that can be used that is not continually run while the query is already open?
或者是否有可以使用的函数在查询已经打开时不会持续运行？
采纳答案by www
There is no Group_Concat in Access :/. Probably there is no solution that excludes VBA.
Here is one possible: Concatenating Rows through a query
Access :/ 中没有 Group_Concat。可能没有排除 VBA 的解决方案。
这是一种可能：通过查询连接行
回答by Kit.net
Here is a rough outline of how to address this using VBA; it performs faster by running a single DB query for the detail records:
以下是如何使用 VBA 解决此问题的粗略概述；通过对详细记录运行单个数据库查询，它的执行速度更快：
Set rsParent = CodeDb.OpenRecordset("SELECT * FROM MainTable ORDER BY HeaderID")
Set rsDetail = CodeDb.OpenRecordset("SELECT * FROM DetailTable ORDER BY HeaderID")
Do Until rsParent.EOF
...
myString = rsParent!MainHeaderName & AggregateDetails(rsDetail, rsParent!HeaderID)
rsParent.MoveNext
Loop
...
Function AggregateDetails(rsDetail as Recordset, HeaderID as String) as String
Dim DetailString as String
Do While rsDetail!HeaderID = HeaderID
DetailString = DetailString & ", " & rsDetail!DetailName
rsDetail.MoveNext
If rsDetail.EOF Then Exit Do
Loop
AggregateDetails = DetailString
End Function
