请教有关BOM SQL查询语句 --急！！(100分)

table :bom <br>t_mitm &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;t_pono &nbsp; &nbsp; &nbsp; &nbsp;t_sitm<br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;10 &nbsp; &nbsp; &nbsp; &nbsp; BA-2-CTN-1 <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; BA-2-WK-1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; BA-1-GBAN-2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;40 &nbsp; &nbsp; &nbsp; &nbsp; PL-410-310 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; PL-404.5-304.5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;60 &nbsp; &nbsp; &nbsp; &nbsp; BLR-SB-600-800 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;70 &nbsp; &nbsp; &nbsp; &nbsp; PTU 160K88351<br>以上是第一阶<br>-------------<br>PTU 160K88351 &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp;913W01057GEX2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>PTU 160K88351 &nbsp; &nbsp; 15 &nbsp; &nbsp; &nbsp; &nbsp; LBL-17-4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>PTU 160K88351 &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp;SMT 160K88351 &nbsp;<br>以上是第二阶<br>-------------- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>SMT 160K88351 &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; E3A3292K1<br>SMT 160K88351 &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp;107E23620TG &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>SMT 160K88351 &nbsp; &nbsp; 30 &nbsp; &nbsp; &nbsp; &nbsp;902W11031G &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>SMT 160K88351 &nbsp; &nbsp; 40 &nbsp; &nbsp; &nbsp; &nbsp;903W02068TG &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>SMT 160K88351 &nbsp; &nbsp; 50 &nbsp; &nbsp; &nbsp; &nbsp;LBL-17-5.5 &nbsp;<br>以上是第二阶<br>如何写语句可得到如下结果(有下阶的，不显示本阶最后一条，如第一阶没有了t_pono=70,第二阶不显示t_pono=20)： <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;10 &nbsp; &nbsp; &nbsp; &nbsp; BA-2-CTN-1 <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; BA-2-WK-1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; BA-1-GBAN-2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;40 &nbsp; &nbsp; &nbsp; &nbsp; PL-410-310 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; PL-404.5-304.5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>160K88351J &nbsp; &nbsp; &nbsp; &nbsp;60 &nbsp; &nbsp; &nbsp; &nbsp; BLR-SB-600-800 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>PTU 160K88351 &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp;913W01057GEX2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>PTU 160K88351 &nbsp; &nbsp; 15 &nbsp; &nbsp; &nbsp; &nbsp; LBL-17-4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>SMT 160K88351 &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; E3A3292K1<br>SMT 160K88351 &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp;107E23620TG &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>SMT 160K88351 &nbsp; &nbsp; 30 &nbsp; &nbsp; &nbsp; &nbsp;902W11031G &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>SMT 160K88351 &nbsp; &nbsp; 40 &nbsp; &nbsp; &nbsp; &nbsp;903W02068TG &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>SMT 160K88351 &nbsp; &nbsp; 50 &nbsp; &nbsp; &nbsp; &nbsp;LBL-17-5.5 &nbsp;<br>也就是，当查询第一阶代码(160K88351J)时，列表中的t_sitm有下阶组成的,则显示下阶组成，小弟在此拜谢!

在线等，请各位大侠支招!

你的表结构是这样的？<br>我的建议表结构如下：<br>BOM 主表<br>BOM品号 BOM序号 其他。。。<br><br>BOM 明细表<br>BOM父阶品号 &nbsp;BOM子阶品号 &nbsp;BOM序号 其他。。。<br><br>这样就可以根据BOM主表中的成品找明细表来找出其半成品和材料。。。<br>然后用树展开。<br>但像你上面的表，看着迷糊。。。。

这个表是迷糊，但没办法，目前在用的数据表，所以请教高手！

唉～其实应该去改变一下数据表结构的。<br>对到BOM的正展，逆展都很需花时间的，如果表的结构不好，那会使时间很长的，长到客户难以接受。<br>对于单个成品或半成品展开来讲我建议是用Store Procedure，毕竟数据多了点。<br>但如果是多个的话，那还是用DELPHI程序来写吧，毕竟SQL是脚本语言，比较慢，不如用DELPHI将BOM表的数据都查出来，再自己来找编译来得快嘛。。。

请问下,你的数据多了,如何判断你 第一阶 第二阶?

重改表结构就可以解决了,不要让业务操作迁就你的持久层结构.业务持作才是最重要的.

CREATE PROCEDURE sp_bom<br>@t_mitm char(30) &nbsp;AS<br>create table #re (t_mitm char(40),t_pono int,t_sitm char(40),level int)<br>declare &nbsp; @l int<br>&nbsp; set &nbsp;@l=1 &nbsp; <br>insert &nbsp;#re &nbsp;<br>select t_mitm,t_pono,t_sitm,@l from bom where t_mitm=@t_mitm &nbsp;<br>while &nbsp; @@rowcount&gt;0<br>begin &nbsp; <br>&nbsp; set @l=@l+1<br>insert #re<br>select a.t_mitm,a.t_pono,a.t_sitm,@l &nbsp; <br>from bom a join #re b on a.t_mitm=b.t_sitm &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>where b.level=@l-1<br>end <br>基本上接近

TO DIGUA<br>数据再多，一个成品的都是由半成品及材料组成的。<br>判断第一阶，第二阶<br>第一阶当然是其父阶为空啦，<br>ID &nbsp; ParentID<br>0 &nbsp; &nbsp;<br>1 &nbsp; &nbsp;0<br>2 &nbsp; &nbsp;0<br>11 &nbsp; 1<br>12 &nbsp; 1<br>21 &nbsp; 2<br>22 &nbsp; 2<br>这样不就能看出第一阶，还有第二阶啥的了嘛。。。