想要实现同一个人多条数据合并?mysql有concat函数
Access的确没有类似wm_concat的函数,需要自己建一个,网上大神已经写好了,亲测可行。效果如下
原表
ID | Name | Role |
---|---|---|
1 | 王楠 | 部门经理 |
2 | 张乐 | 营销主任 |
3 | 张乐 | 市场推广员 |
4 | 欧阳华 | 技术主管
|
建立如上的表,并添加如下的函数:
打开vba,插入模块, 然后点插入,选择函数(全局的最好)。点工具后点引用,引用之后选activeX data obj。
Public Function GroupConcat(sColumn As String, sTable As String, Optional sCriteria As String, Optional sDelimiter As String = ",") On Error GoTo ErrHandler Dim rs As New ADODB.Recordset Dim sSQL As String Dim sResult As String sResult = "" sSQL = "select " & sColumn & " from " & sTable If sCriteria <> "" Then sSQL = sSQL & " where " & sCriteria End If rs.Open sSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly Do While Not rs.EOF If sResult <> "" Then sResult = sResult & sDelimiter End If sResult = sResult & rs.Fields(0).Value rs.MoveNext Loop rs.Close Set rs = Nothing GroupConcat = sResult Exit Function ErrHandler: If rs.State <> adStateClosed Then rs.Close End If Set rs = Nothing GroupConcat = Err.Number & " : " & Err.Description End Function
添加如下查询
SELECT Name, GroupConcat('Role','T_Edwin','Name=' & "'" & Name & "'") AS Roles FROM T_Edwin GROUP BY Name;
查询结果如下:
Name | Roles |
---|---|
欧阳华 | 技术主管 |
王楠 | 部门经理 |
张乐 | 营销主任,市场推广员 |