sql – MS访问(2003)是否具有与存储过程相当的任何内容.我想在MS

发布时间：2020-12-12 08:48:36 所属栏目：MsSql教程来源：网络整理

导读：我有一张桌子,叫做TBL.它有两列,称为A和B.现在在查询中,我需要一列作为A,其他列应该是TBL中与A对应的所有B的逗号分隔列表. 例如TBL就是这样 1阿尔法 2 Beta 1伽玛 1达美查询结果应该是 1 Alpha,Gamma,Delta 2 Beta 这种类型的事情在存储过程中与光标非常容易

我有一张桌子,叫做TBL.它有两列,称为A和B.现在在查询中,我需要一列作为A,其他列应该是TBL中与A对应的所有B的逗号分隔列表.
例如TBL就是这样

1阿尔法

2 Beta

1伽玛

1达美

查询结果应该是

1 Alpha,Gamma,Delta

2 Beta

这种类型的事情在存储过程中与光标非常容易.但是我无法通过MS Access进行操作,因为显然它不支持存储过程.
有没有办法在MS访问中运行存储过程？还是有办法通过SQL来运行这种类型的查询

解决方法

您可以将记录与用户定义函数(UDF)连接起来.

以下代码可以“按原样”粘贴到标准模块中.您的示例的SQL将是：

SELECT tbl.A,Concatenate("SELECT B  FROM tbl
        WHERE A = " & [A]) AS ConcA
FROM tbl
GROUP BY tbl.A

这个代码是由DHookom,Access MVP,从http://www.tek-tips.com/faqs.cfm?fid=4233开始

Function Concatenate(pstrSQL As String,_
        Optional pstrDelim As String = ",") _
            As String
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID,FirstName,DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    '    John,Mary,Susan
    'in a Query
    '(This SQL statement assumes FamID is numeric)
    '===================================
    'SELECT FamID,'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '===================================
    '
    'If the FamID is a string then the SQL would be
    '===================================
    'SELECT FamID,'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =""" & [FamID] & """") as FirstNames
    'FROM tblFamily
    '===================================

    '======For DAO uncomment next 4 lines=======
    '======     comment out ADO below    =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '======     comment out DAO above     ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL,CurrentProject.Connection,_
            adOpenKeyset,adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat,_
            Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!