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

问题描述：

说，有一个扁平顺序层表以特殊字符“+”表示层级SQL for MSACCESS如何实现字符分隔的数据层次结构？

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

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

可以有多少层次，并且总会有最多层次的行（即如果3层，总会有3行来填充你的表）？我知道你可以使用VBA在Access中处理，并且可以使用Excel重新格式化为扁平行。 –

什么标准决定那些Level2数字与ASSETS_01而不是ASSETS_02？ – June7

需要检查FIELD1成员以计算MAX出现的“+”。这将决定*别数量。但这是非常预先确定的。假设*别数为5（作为例子），它不会改变。 –

答

好奇心越来越好，所以我探索了查询方法。我诉诸于使用域聚合函数。请注意，对于大型数据集，域聚合函数可以缓慢执行。然而，考虑：

查询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;

QUERY2：

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));

QUERY3：

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;

Query4：

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;

我绝对不确定Level3及以后的治疗方法。可能是VBA将只能解决的方法。

答

以下代码已经过测试，并与您提到的数据结构一起工作。目前它最多可以处理10个级别，但可以轻松更改。棘手的部分是不写出的记录，直到你有该行的所有级别（新行开始不同的level1值，或提供多个level-n值）。最后一行是在输入之后编写的。

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

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

相关推荐