Access的SQL特殊查询语句
答案:2 悬赏:70 手机版
解决时间 2021-04-04 19:53
- 提问者网友：爱了却不能说
- 2021-04-04 09:56
Access的SQL特殊查询语句
最佳答案
- 五星知识达人网友：躲不过心动
- 2021-04-04 10:39
ACCESS 估计没有办法去实现（可能小弟才疏学浅）!!!
建议 LZ 从程序上入手，或者 转其他数据库， 没必要在Access上浪费时间。
建议 LZ 从程序上入手，或者 转其他数据库， 没必要在Access上浪费时间。
全部回答
- 1楼网友：低血压的长颈鹿
- 2021-04-04 10:51
这个要利用sql结合自定义函数的方法去实现。
Public Function GetList(SQL As String _
, Optional ColumnDelimeter As String = ", " _
, Optional RowDelimeter As String = vbCrLf) As String
Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String
On Error GoTo ProcErr
Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)
sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)
If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If
GetList = sResult
oRS.Close
oConn.Close
CleanUp:
Set oRS = Nothing
Set oConn = Nothing
Exit Function
ProcErr:
Resume CleanUp
End Function
使用方法：
SELECt SourceTable.Name
, GetList("Select Day From SourceTable As T1 Where T1.Name = """ & [SourceTable].[Name] & """","",", ") AS Expr1
FROM SourceTable
GROUP BY SourceTable.Name;
Public Function GetList(SQL As String _
, Optional ColumnDelimeter As String = ", " _
, Optional RowDelimeter As String = vbCrLf) As String
Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String
On Error GoTo ProcErr
Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)
sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)
If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If
GetList = sResult
oRS.Close
oConn.Close
CleanUp:
Set oRS = Nothing
Set oConn = Nothing
Exit Function
ProcErr:
Resume CleanUp
End Function
使用方法：
SELECt SourceTable.Name
, GetList("Select Day From SourceTable As T1 Where T1.Name = """ & [SourceTable].[Name] & """","",", ") AS Expr1
FROM SourceTable
GROUP BY SourceTable.Name;
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯