急迫需解决的一个递归问题，请帮帮忙啊

[gukuta]

请高手帮帮忙啊

SQL中两张表。如下：
父 子
000 0001
000 0002
000 0003
0001 00011
0002 00021
0002 00022
00021 000211


夫 妻
000 a
000 b
0001 c
0002 d
00021 e

在EXCEL中输出为 ：
阶次 名单
0 000
* a
* b
1 0001
* c
2 00011
1 0002
* d
2 00021
* e
3 000211
2 00022
1 0003

[gukuta]

drop table fuzi
go
create table fuzi(fuqin char(10),erzi char(10))
insert into fuzi(fuqin,erzi) values('000','0001')
insert into fuzi(fuqin,erzi) values('000','0002')
insert into fuzi(fuqin,erzi) values('000','0003')
insert into fuzi(fuqin,erzi) values('0001','00011')
insert into fuzi(fuqin,erzi) values('0002','00021')
insert into fuzi(fuqin,erzi) values('0002','00022')
insert into fuzi(fuqin,erzi) values('00021','000211')
insert into fuzi(fuqin,erzi) values('00021','000212')

go

drop table fuqi
go
create table fuqi(zhangfu char(10),qizi char(10))
insert into fuqi(zhangfu,qizi) values('000','a')
insert into fuqi(zhangfu,qizi) values('000','b')
insert into fuqi(zhangfu,qizi) values('0001','c')
insert into fuqi(zhangfu,qizi) values('0002','d')
insert into fuqi(zhangfu,qizi) values('00021','e')
go

[northwolves]

Excel VBA:

Dim arr(1 To 65536, 1 To 2), n As Long, fuzi, fuqi
Sub Addtree(ByVal level As Long, ByVal him As String)
n = n + 1'新行
arr(n, 1) = level
arr(n, 2) = him

For i = 0 To UBound(fuqi, 2)
If CStr(fuqi(0, i)) = him Then'妻妾
n = n + 1
arr(n, 1) = "*"
arr(n, 2) = CStr(fuqi(1, i))
End If
Next
For i = 0 To UBound(fuzi, 2)
If CStr(fuzi(0, i)) = him Then Addtree level + 1, fuzi(1, i)'递归调用
Next
End Sub

Sub macro1()
Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "....." '你的连接字符串
fuqi = cnn.Execute("select * from fuqi").getrows
fuzi = cnn.Execute("select * from fuzi").getrows
arr(1, 1) = "阶次"
arr(1, 2) = "名单"
[a:b].NumberFormatLocal = "@"
n = 1
Addtree 0, "000"
[a1].Resize(n, 2) = arr
End Sub

[gukuta]

谢谢你，狼行天下

两边都见你回复了。

在递归调用的时候有点问题。

[unixlinuxsys]

解决了？

[gukuta]

暂时还没。

在执行“递归调用”那段出了问题

[northwolves]

有可能是你的记录中存在空格或其他非打印字符。
我传了一个附件：
sample.rar

[northwolves]

返回结果不对与你的字段指定大小有关，再试试

Dim arr(1 To 65536, 1 To 2), n As Long, fuzi, fuqi
Sub Addtree(ByVal level As Long, ByVal him As String)
    n = n + 1    '新行
    arr(n, 1) = level
    arr(n, 2) = him

    For i = 0 To UBound(fuqi, 2)
        If Trim(fuqi(0, i)) = him Then    '妻妾
            n = n + 1
            arr(n, 1) = "*"
            arr(n, 2) = Trim(fuqi(1, i))
        End If
    Next
    For i = 0 To UBound(fuzi, 2)
        If Trim(fuzi(0, i)) = him Then Addtree level + 1, Trim(fuzi(1, i))    '递归调用
    Next
End Sub


Sub macro1()
    Dim cnn As Object
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "....."    '你的连接字符串
    fuqi = cnn.Execute("select * from fuqi").getrows
    fuzi = cnn.Execute("select * from fuzi").getrows
    arr(1, 1) = "阶次"
    arr(1, 2) = "名单"
    [a:b].NumberFormatLocal = "@"
    n = 1
    Addtree 0, "000"
    [a1].Resize(n, 2) = arr
End Sub

[gukuta]

狼行，谢谢帮助！

[gukuta]

在线等，请大家帮帮忙咯

[shunwu2009]

function childs(uid)
{
select * FROM where id=uid;
childs(id);
}

childs(000);
这是递归的原型，excel中可能将数据先导入表，再做，
你先理理，上传个附件

[shunwu2009]

你可以将excel的数据源做好，qq联系我754211506，中午帮你做一下