时间戳

首页 > 解决方案 > 循环查找相关记录

sql - 循环查找相关记录

问题描述

寻找建立循环的帮助/方向？函数在表中查找相关记录。

表 ( tblTransactions) 包含有关我们正在跟踪的各种事务的信息。我还使用此表来引用前一交易。现在我正在寻找一种方法来遍历表以查找相关记录。

该表具有以下字段：

一些样本数据：

+---------+---------+---------+--------+
| TransID | Grantor | Grantee | PTrans |
+---------+---------+---------+--------+
|       1 | Bob     | Sally   |      0 |
|       2 | Jane    | Emily   |      0 |
|       3 | Sally   | Beth    |      1 |
|       4 | Beth    | Sam     |      3 |
+---------+---------+---------+--------+

理想情况下，我希望能够从 TransID 开始，并在单独的行上显示所选事务 ( ) 及其前身的4所有事务数据。4

结果将是：

+---+-------+-------+
| 4 | Beth  | Sam   |
| 3 | Sally | Beth  |
| 1 | Bob   | Sally |
+---+-------+-------+

标签： sqlvbaloopsms-access

解决方案

您关于查询自引用数据的问题与这个问题非常相似，其中用户有一个员工表，每个员工可能有一个主管，其员工记录也存在于同一个表中，从而形成层次结构。

解决此问题的相对简单的方法是DLookup在循环或递归调用中使用表达式，直到表达式返回Null。例如，这是一个递归变体：

Function TransLookup(lngtrn As Long)
    Dim lngptr
    lngptr = DLookup("ptrans", "tbltransactions", "transid = " & lngtrn)

    If Not IsNull(lngptr) Then
        Debug.Print lngtrn ' Do something with the data
        TransLookup (lngptr)
    End If
End Function

用您的数据评估这将产生：

?TransLookup(4)
 4 
 3 
 1

这当然只是打印事务 ID，但如果需要，该函数也可以用每个事务的数据填充一个单独的表。

但是，如果我们可以构造一个 SQL 查询来一次性返回所有结果，那么逐条返回结果或填充临时表似乎并不优雅。

但是，由于 MS Access 不支持递归 SQL 查询，因此查询此类分层数据时的困难在于不知道要提前编写多少级别。

因此，您可以使用 VBA 函数来构造 SQL 查询本身，从而始终合并尽可能多的级别以返回完整的数据集。

事实上，这是我在回答上面链接的相关问题时提出的方法 - 该答案中提供的功能同样可以适应这种情况，例如：

Function BuildQuerySQL(lngtrn As Long) As String
    Dim intlvl As Integer
    Dim strsel As String: strsel = selsql(intlvl)
    Dim strfrm As String: strfrm = "tbltransactions as t0 "
    Dim strwhr As String: strwhr = "where t0.transid = " & lngtrn

    While HasRecordsP(strsel & strfrm & strwhr)
        intlvl = intlvl + 1
        BuildQuerySQL = BuildQuerySQL & " union " & strsel & strfrm & strwhr
        strsel = selsql(intlvl)
        If intlvl > 1 Then
            strfrm = "(" & strfrm & ")" & frmsql(intlvl)
        Else
            strfrm = strfrm & frmsql(intlvl)
        End If
    Wend
    BuildQuerySQL = Mid(BuildQuerySQL, 8)
End Function

Function HasRecordsP(strSQL As String) As Boolean
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    With dbs.OpenRecordset(strSQL)
        HasRecordsP = Not .EOF
        .Close
    End With
    Set dbs = Nothing
End Function

Function selsql(intlvl As Integer) As String
    selsql = "select t" & intlvl & ".* from "
End Function

Function frmsql(intlvl As Integer) As String
    frmsql = " inner join tbltransactions as t" & intlvl & " on t" & intlvl - 1 & ".ptrans = t" & intlvl & ".transid "
End Function

BuildQuerySQL现在，使用事务 ID评估函数4会产生以下 SQLUNION查询，每个级别的嵌套都与前一个查询联合：

select 
    t0.* 
from 
    tbltransactions as t0 
where 
    t0.transid = 4 

union 

select 
    t1.* 
from 
    tbltransactions as t0 inner join tbltransactions as t1 
    on t0.ptrans = t1.transid 
where 
    t0.transid = 4 

union 

select 
    t2.* 
from
    (
        tbltransactions as t0 inner join tbltransactions as t1 
        on t0.ptrans = t1.transid 
    ) 
    inner join tbltransactions as t2 
    on t1.ptrans = t2.transid 
where 
    t0.transid = 4

因此，可以评估此​​类函数以构造保存的查询，例如对于事务 ID = 4，以下将创建一个名为 的查询TransactionList：

Sub test()
    CurrentDb.CreateQueryDef "TransactionList", BuildQuerySQL(4)
End Sub

或者，根据应用程序的要求，可以评估 SQL 以打开结果的 RecordSet。

当使用您的示例数据进行评估时，上述 SQL 查询将产生以下结果：

+---------+---------+---------+--------+
| TransID | Grantor | Grantee | PTrans |
+---------+---------+---------+--------+
|       1 | Bob     | Sally   |      0 |
|       3 | Sally   | Beth    |      1 |
|       4 | Beth    | Sam     |      3 |
+---------+---------+---------+--------+

推荐阅读