MySQL ms-access 中有 group_concat 函数吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2852892/
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
is there a group_concat function in ms-access?
提问by l--''''''---------''''''''''''
is there a group_concat function in ms-access or something similar?
ms-access 中是否有 group_concat 函数或类似的东西？
采纳答案by David-W-Fenton
You should ask yourself if you need a generic solution (another is by Allen Browne) or if you need it just for the present purpose. If you really only need it this once, do it the easy way.
您应该问问自己是否需要一个通用的解决方案（另一个是 Allen Browne 提出的），或者您是否需要它只是为了当前的目的。如果您真的只需要它一次，请使用简单的方法。
On a side note, when concatenating lists in VBA code, take advantage of a trick taught to me by long-time Access guru Trevor Best, and that's to stick the delimiter at the beginning of every value and then use Mid() to strip it off. Instead of this inside your loop through the child records:
附带说明一下，在 VBA 代码中连接列表时，请利用长期访问大师 Trevor Best 教给我的技巧，那就是在每个值的开头粘贴分隔符，然后使用 Mid() 将其剥离离开。而不是在你的循环中通过子记录：
If Len(strOutput) = 0 Then
strOutput = NewValue
Else
strOutput = strOutput & ", " & NewValue
End If
...use this inside the loop:
...在循环中使用它：
strOutput = strOutput & ", " & NewValue
...and then when you exit the loop, strip off the leading delimiter:
...然后当你退出循环时，去掉前导分隔符：
strOutput = Mid(strOutput, 3)
This has implications all over the place and simplifies code for concatenation in a whole host of contexts.
这对所有地方都有影响，并简化了大量上下文中的连接代码。
回答by Mark Byers
I found this postby Duane Hookum (a Microsoft MVP) that claims to be able to do what you want. I have not tested it though.
我发现Duane Hookum（微软 MVP）的这篇文章声称能够做你想做的事。不过我还没有测试过。
By the way, in case you are interested, this is how I found it:
顺便说一句，如果你有兴趣，我是这样找到它的：
First search: group_concat accesslead me to this postwith this answerbut the link was broken.
第一次搜索：group_concat 访问使我通过这个答案找到了这篇文章，但链接已损坏。
Then I searched again after the content that the answer was attempting to link to, and found it: site:http://www.rogersaccesslibrary.com/ concatenate.
然后我在答案试图链接到的内容之后再次搜索，并找到了它：site:http://www.rogersaccesslibrary.com/ concatenate。
回答by mdma
There's an access function to group multiple values into one value (a custom aggregate, I guess.) The link is http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'
有一个访问函数可以将多个值分组为一个值（我猜是自定义聚合。）链接是http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child% 20条记录
but the site is down for now. If you google the href, you'll find lots of referneces and examples.
但该网站暂时关闭。如果您在 google 上搜索 href，您会发现很多参考资料和示例。
回答by Thomas
No. Access does not have a GROUP_CONCAT function. However, it is possible to create a VBA function which will let you pass a string containing a SQL statement and get the equivalent functionality (not that I'd recommend it but it is possible).
否。Access 没有 GROUP_CONCAT 功能。但是，可以创建一个 VBA 函数，它可以让您传递一个包含 SQL 语句的字符串并获得等效的功能（不是我推荐它，但它是可能的）。
Taking my own personal wayback machine, here is some code I wrote back when dinosaurs ruled the Earth:
拿我自己的个人回归机器，这是我在恐龙统治地球时写回的一些代码：
Public Function ListQuery(SQL As String _
, Optional ColumnDelimiter As String = " " _
, Optional RowDelimter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
' 1. SQL is a valid Select statement
' 2. ColumnDelimiter is the character(s) that separate each column
' 3. RowDelimiter is the character(s) that separate each row
'RETURN VAL:
'DESIGN NOTES:
Const PROCNAME = "ListQuery"
Const MAXROWS = 100
Const MAXCOLS = 10
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oField As ADODB.Field
Dim sRow As cString
Dim sResult As cString
On Error GoTo ProcErr
Set sResult = New cString
Set sRow = New cString
Set oConn = GetADOConn()
sResult.Clear
Do Until oRS.EOF
sRow.Clear
For Each oField In oRS.Fields
With sRow
If .Length > 0 Then
.Append ColumnDelimiter
End If
.Append Nz(oField.Value)
End With
Next oField
sRow.Trim
If sRow.Length > 0 Then
With sResult
.Append sRow
.Append RowDelimter
End With
End If
oRS.MoveNext
Loop
oRS.Close
oConn.Close
With sResult
If .Right(Len(RowDelimter)).Value = RowDelimter Then
.Length = .Length - Len(RowDelimter)
End If
End With
FunctionResult:
ListQuery = sResult.Value
CleanUp:
Set sResult = Nothing
Set sRow = Nothing
Set oField = Nothing
Set oRS = Nothing
Set oConn = Nothing
Exit Function
ProcErr:
' logging code...
Resume CleanUp
End Function
The GetADOConnfunction is a centralized function to retrieve the current database connection. cStringis a class that mimics the behavior of .NET's StringBuilderclass but was written long before .NET was anything other than a TLD and marketing hype. Since this is getting called on every row, VBA's built-in string concatenation will be slow and thus something like a StringBuilderclass is needed. The original code (which I've partially modified) had a cap on the number of rows and columns that could be used which is what the constants are all about.
该GetADOConn函数是检索当前数据库连接的集中函数。cString是一个模仿 .NETStringBuilder类行为的类，但在 .NET 成为 TLD 和营销炒作之外的任何东西之前很久就被编写了。由于这在每一行都被调用，VBA 的内置字符串连接会很慢，因此StringBuilder需要类似类的东西。原始代码（我已经部分修改）对可以使用的行数和列数有一个上限，这就是常量的全部内容。
