怎样使用SQL语句进行分层分组

[benben_2]

1. 现有A、B、C三个产品

2. A产品采用1、2、3组件、
B产品采用1、3、4组件、
C产品采用2、3、5组件、

3. 1组件使用X、Y、Z零件、
2组件使用L、Y、Z零件、
3组件使用L、M、Z零件、
4组件使用X、Y、N零件、
5组件使用L、M、N零件、

4.现在数据库DATA中

up(上层部件) down(下层部件)
A 1
A 2
A 3
B 1
B 3
B 4
C 2
C 3
C 5
1 X
1 Y
1 Z
2 L
2 Y
2 Z
3 L
3 M
3 Z
4 X
4 Y
4 N
5 L
5 M
5 N

问我怎样使用SQL语句对其数据库DATA进行分层分组使其显示出来的数据为
A---1---X
| |-Y
| |-Z
|
|-2---L
| |-Y
| |-Z
|
|-3---L
| |-M
| |-Z


B---1---X
| |-Y
| |-Z
|
|-3---L
| |-M
| |-Z
|
|-4---X
| |-Y
| |-N


X---2---L
| |-Y
| |-Z
|
|-3---L
| |-M
| |-Z
|
|-5---L
| |-M
| |-N
的结构

[kelly_1127]

不会

[死胖子]

递归从数据库中读出部件，真到其没有下级部件为止

ASP的代码我不会，呵PHP倒 是还行

[BelieveCom]

在SQL有存儲過程中實現了即可，界面上形成記錄不難。
將你的數據表結構send email給我，我幫你解決！

[e-mail]

[hqcsx]

你可以参照以下代码:
<table border="0" cellpadding="0" cellspacing="0" width="100%" height="445">
<center>
<tr>
<td width="100%" height="420" valign="top"><%dim upid(100),nowid(100),i,t,idnum,nowname(100),r,h,tmp,x(20,20)
for i=0 to 19
for j=0 to 19
x(i,j)=0
next
next
set rs=server.createobject("adodb.recordset")
qq="select UnitsName,id from HR_Units where Units_up_id='0'"
rs.open qq,cn,1,1
if not(rs.eof and rs.bof) then
upid(0)=0
nowid(0)=rs("id")
upid(1)=nowid(0)
nowid(1)=0
a=1
j=1
t=0
x(0,0)=2
do while upid(j)<>0
set rs2=server.createobject("adodb.recordset")
if a=0 then
qq="select id from HR_Units where Units_up_id='"+cstr(upid(j))+"' and id>'"+cstr(nowid(j))+"' order by id asc"

else
qq="select id from HR_Units where Units_up_id='"+cstr(upid(j))+"' order by id asc"
end if
rs2.Open qq,cn,1,1
if (rs2.eof and rs2.bof) then
j=j-1
if a=1 then t=t+1
a=0
else
nowid(j)=rs2("id")
x(j,t)=1
if i<j then i=j
if j=0 then
x(j,t)=2
elseif x(j-1,t)>0 then
x(j,t)=1
else
x(j,t)=2
end if
j=j+1
upid(j)=nowid(j-1)
a=1
end if
rs2.close
loop
rs.close
%>
<div align="left">
<center>
<table border="0" cellpadding="0" cellspacing="0" width="98%" align="center" class="n1">

<%
upid(0)=0
a=1
j=0
r=0
do while j>=0
set rs2=server.createobject("adodb.recordset")
if a=0 then
qq="select Photo,UnitsName,id from HR_Units where Units_up_id='"+cstr(upid(j))+"' and id>'"+cstr(nowid(j))+"' order by id asc"
rs2.Open qq,cn,1,1
if x(j,r)=2 then
response.write "</tr>"

for k=0 to j-1%><td width="<%=int(100/(i+1))%>%" height="<%=int(90/t)%>%" class="n1">
<%tmp=0
for h=r to t
if x(k,h)>0 and tmp=0 then tmp=x(k,h)
next
if tmp=2 and x(k,r-1)>0 then%>
<img border="0" src="../images/h.gif" align="left" WIDTH="20" HEIGHT="20">
<%x(k,r)=2
end if%>
</td>
<%next
end if%>

<%
else
qq="select Photo,UnitsName,id from HR_Units where Units_up_id='"+cstr(upid(j))+"' order by id asc"
rs2.Open qq,cn,1,1
end if
if (rs2.eof and rs2.bof) then
j=j-1
if a=1 then r=r+1
a=0
response.write "</tr><tr height='2'></tr>"
else

nowid(j)=rs2("id")
nowname(j)=rs2("Photo")%>
<td width="<%=int(100/(i+1))%>%" class="n1" align="right"><table border="0" cellpadding="0" cellspacing="0" width="100%" class="n1"><tr><td width="50" align="left">
<%if j<>0 then
if x(j,r)=2 then%>
<img border="0" src="../images/t.gif" align="left" WIDTH="20" HEIGHT="20"><img border="0" src="../images/y.gif" align="left" WIDTH="20" HEIGHT="23">
<%else%>
<img border="0" src="../images/y.gif" align="left" WIDTH="20" HEIGHT="20"><img border="0" src="../images/y.gif" align="left" WIDTH="20" HEIGHT="20">
<%end if
end if%></td><td width="80%" style="border-style: solid; border-width: 1" align="center" class="n1">
<a href='javascript:void(0);' onclick="" onmouseover='ShowPrompt();' onmouseout='HidePrompt();'
oncontextmenu='PopMenu(<%=rs2("ID")%>);return false;'>
<%if nowname(j)<>"" then%>
<img src="../File_up/Units/<%=rs2("ID")%>/<%=nowname(j)%>" border=0></a>
<%else%><a href="#" onClick="OpenWindow('PictureUpfrm.asp?ID=<%=rs2("ID")%>','PhotoWindow')"><%response.write rs2("UnitsName")%></a><%end if%></a></td></tr></table></td>
<%j=j+1
upid(j)=nowid(j-1)
a=1
end if
rs2.close
loop
else
Response.Write "<br><br><p align=center><font color=ff0000>现在无任何部门</font></p>"
end if%>

</table></td>
</tr>
</table>

[bingochen]

...........
用一个递归函数就可以全部读出来了