已知四张表:
select pronum from [ProjectReg]
select pronum,pronum1 from [ProjectJiZai]
select pronum,pronum1,pronum2 from [rollreg]
select pronum,pronum1,pronum2,pronum3 from [filesreg]
----------------------------------------------------------SQL查询结果
A
A 22222
A 22222
A 22222 33
A 22222 44
A 22222 33 333
A 22222 44 444
--------------------------------------先用VB的TreeVieww生成四级树
A
22222
33
333
44
444
--------------------------------------下面是实现代码:
'根据现有数据库结构生成四级目录树TreeView
Private Sub LoadFromTable()
Dim oNodex As ComctlLib.Node
Dim nImage, nImage1, nImage2, nImage3 As String
TreeView1.Nodes.Clear
Set mRS = cnMain.Execute("select pronum from [ProjectReg]")
If mRS.RecordCount > 0 Then
mRS.MoveFirst
Do While mRS.EOF = False
nImage = mRS.Fields("pronum")
'一级目录开始
Set oNodex = TreeView1.Nodes.Add(, , "R", nImage, 1)
'二级目录开始
Set mRS1 = cnMain.Execute("select distinct pronum1 as pronum1 from [ProjectJiZai] where pronum='" & nImage & "'")
If mRS1.RecordCount > 0 Then
mRS1.MoveFirst
Do While mRS1.EOF = False
nImage1 = mRS1.Fields("pronum1")
Set oNodex1 = TreeView1.Nodes.Add("R", tvwChild, , nImage1, 2)
' '三级目录开始
Set mRS2 = cnMain.Execute("select distinct pronum2 as pronum2 from [rollreg] where pronum='" & nImage & "' and pronum1='" & nImage1 & "' ")
If mRS2.RecordCount > 0 Then
mRS2.MoveFirst
Do While mRS2.EOF = False
nImage2 = mRS2.Fields("pronum2")
Set oNodex2 = TreeView1.Nodes.Add(oNodex1, tvwChild, , nImage2, 2)
'四级目录开始
Set mRS3 = cnMain.Execute("select distinct pronum3 as pronum3 from [filesreg] where pronum='" & nImage & "' and pronum1='" & nImage1 & "' and pronum2='" & nImage2 & "' ")
If mRS3.RecordCount > 0 Then
mRS3.MoveFirst
Do While mRS3.EOF = False
nImage3 = mRS3.Fields("pronum3")
Set oNodex3 = TreeView1.Nodes.Add(oNodex2, tvwChild, , nImage3, 2)
mRS3.MoveNext
Loop
End If
mRS3.Close
' '四级目录结束
mRS2.MoveNext
Loop
End If
mRS2.Close
' '三级目录结束
mRS1.MoveNext
Loop
End If
mRS1.Close
'二级目录结束
mRS.MoveNext
Loop
mRS.Close
'一级目录结束
End If
End Sub