SQL for MSACCESS 如何实现字符分隔的数据层次结构

Question

比如说，有一个扁平化的顺序层次结构表，其中有一个特殊字符"+"表示层次结构级别

hr_table
ID   FIELD1        My irrelevant comments
----------------
1    ASSETS        No pluses - it means level0 hierarchy
2    +ASSETS_01    Level1 hierarchy
3    ++345667654   Level2 hierarchy
4    ++563255512   Level2 hierarchy
5    ...

有没有办法使用 SQL 在 MSACCESS 中创建字段结构？我正在尝试按如下方式构建最终数据：

final_data_table
ID  LEVEL0     LEVEL1       LEVEL2    ...
-------------------------------------------
1   ASSETS     ASSETS_01    345667654
2   ASSETS     ASSETS_01    563255512

任何/所有的帮助非常感谢！

Answer 1

好奇心让我变得更好，所以我探索了查询方法。我求助于使用域聚合函数。请注意，域聚合函数在处理大型数据集时可能会执行缓慢。但是，请考虑：

查询 1：

SELECT hr_table.ID, IIf([Field1] Like "+*",Left([Field1],InStrRev([Field1],"+")),0) AS Prefix, IIf([Field1] Like "+*",Null,[Field1]) AS Asset1, IIf(InStrRev([Field1],"+")=1,Mid([Field1],2),Null) AS Asset2, IIf([Field1] Like "++*",Mid([Field1],InStrRev([Field1],"+")+1),Null) AS Data
FROM hr_table;

查询2：

SELECT Query1.ID, Query1.Prefix, DMax("Asset1","Query1","ID<=" & [ID]) AS A1, DMax("Asset2","Query1","ID<=" & [ID]) AS A2, Query1.Data
FROM Query1
WHERE ((Not (Query1.Data) Is Null));

查询3：

SELECT Query2.Prefix, Query2.A1, Query2.A2, Query2.Data, DCount("*","Query2","A1='" & [A1] & "' AND A2='" & [A2] & "' AND Prefix = '" & [Prefix] & "' AND ID<=" & [ID]) AS GrpSeq
FROM Query2;

查询4：

TRANSFORM Max(Query3.Data) AS MaxOfData
SELECT Query3.A1, Query3.A2, Query3.GrpSeq
FROM Query3
GROUP BY Query3.A1, Query3.A2, Query3.GrpSeq
PIVOT Query3.Prefix;

我绝对不确定3级及以上的治疗。可能是VBA将是解决的唯一方法。

Answer 2

以下代码已经过测试，适用于您提到的数据结构。它目前设置为处理多达 10 个级别，但可以轻松更改。棘手的部分是不要写出记录，直到您拥有该一行的所有级别(新行以不同的 level1 值开头，或者当提供多个级别 n 值时(。最后一行写在输入的 eof 之后。

Option Compare Database
Option Explicit
Function Parse_Fields()
Dim dbs As DAO.Database
Dim rsIN    As DAO.recordSet
Dim rsOUT   As DAO.recordSet
Dim i       As Integer
Dim iPlus   As Integer
Dim aLevels(10)
Dim bAdding As Boolean
    Set dbs = CurrentDb
    Set rsIN = dbs.OpenRecordset("hr_table")
    Set rsOUT = dbs.OpenRecordset("final_data_table")
    bAdding = False
    Do While Not rsIN.EOF
        'Debug.Print "Input: " & rsIN!field1
        If left(rsIN!field1, 1) <> "+" Then
            ' Check if not first time thru... if not, write prior levels..
            If bAdding = True Then
                rsOUT.Update
            End If
            iPlus = 0
            rsOUT.AddNew
            rsOUT!Level0 = rsIN!field1
            bAdding = True
            ' Don't issue the .Update yet! Wait until another Level0 or EOF.
        Else
            For iPlus = 1 To 10         ' Change code if more than ten levels
                If Mid(rsIN!field1, iPlus, 1) <> "+" Then Exit For
            Next iPlus
            ' Check if same level as previous!  If so, do NOT overlay!
            If Not IsNull(rsOUT.Fields(iPlus)) Then
                For i = 1 To iPlus - 1      ' Save the proper levels for the new record.
                    aLevels(i) = rsOUT.Fields(i)
                Next i
                rsOUT.Update                ' Need to write out record.
                rsOUT.AddNew
                For i = 1 To iPlus          ' Populate the new record with prior levels
                     rsOUT.Fields(i) = aLevels(i)
                Next i
                rsOUT.Fields(iPlus) = Mid(rsIN!field1, iPlus)       ' Add the new level
            Else
                rsOUT.Fields(iPlus) = Mid(rsIN!field1, iPlus)
            End If
        End If
        rsIN.MoveNext               ' Get next input
    Loop
    ' Need to write out the final record we have beenbuilding!
    rsOUT.Update
    rsIN.Close
    rsOUT.Close
    Set rsIN = Nothing
    Set rsOUT = Nothing
    Set dbs = Nothing
    Debug.Print "FINISHED!!"
End Function