0

我的帖子

个人中心

设置

  发新话题

数据如下:


CREATE TABLE [dbo].[table_bom1]([/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]        [bom_no] [varchar](38) NOT NULL,[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]        [prd_no] [varchar](30) NULL,[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]        [id_no] [varchar](38) NULL,[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]        [wh_no] [varchar](12) NULL,[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]        [qty] [numeric](22, 8) NULL,[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]        [qty_bas] [numeric](22, 8) NULL[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]) ON [PRIMARY][/size][/font][/align]

[align=left][font=Helvetica, Arial, sans-serif][size=14px]GO




insert into table_bom1 values('10-W03A000-00004->','09-W03A000-00001','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','12-0100000-00005','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','12-0400000-00004','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','12-0500000-00008','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','28-0010200-00002','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','13-0200000-00001','','A1',5,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00020','','A1',2,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00004','','A1',11,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00011','','A1',17,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00042','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00081','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00013','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00043','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00031','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00026','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00062','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00006','','A1',2,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00052','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00059','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00013','','A1',8,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','30-0040000-00001','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','18-0300000-00003','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','20-0100000-00004','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','19-0100001-00004','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','17-0100000-00027','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','17-0100000-00063','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','13-0100000-00002','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','16-0100000-00001','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00075','','A1',6,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','12-0100000-00011','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00011','','A1',3,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','14-0100000-00086','','A1',3,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00011','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00012','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00013','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','15-0100000-00095','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','16-0400000-00004','','A1',2,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','17-0100000-00004','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','17-0100000-00009','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03A000-00004->','21-0100000-00021','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03B000-00001->','12-0600000-00012','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03B000-00001->','18-0100000-00018','','A1',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]insert into table_bom1 values('10-W03B000-00001->','10-W03A000-00004','10-W03A000-00004->','D',1,1)[/size][/font][/align]
[align=left][font=Helvetica, Arial, sans-serif][size=14px]




希望查询出来的结构如下图:






BOM_NO 代表  BOM代码,prd_no代表子件,ID_NO代表这个子件有BOM,并且BOM_NO=ID_NO



小弟对SQL只懂最基本的select,请有能力的帮忙看下如何查询出来




论坛插入的代码有问题,我将  数据语句再传一遍看看:

CREATE TABLE [dbo].[table_bom1](
[bom_no] [varchar](38) NOT NULL,
[prd_no] [varchar](30) NULL,
[id_no] [varchar](38) NULL,
[wh_no] [varchar](12) NULL,
[qty] [numeric](22, 8) NULL,
[qty_bas] [numeric](22, 8) NULL
) ON [PRIMARY]
GO

insert into table_bom1 values('10-W03A000-00004->','09-W03A000-00001','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','12-0100000-00005','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','12-0400000-00004','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','12-0500000-00008','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','28-0010200-00002','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','13-0200000-00001','','A1',5,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00020','','A1',2,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00004','','A1',11,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00011','','A1',17,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00042','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00081','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00013','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00043','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00031','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00026','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00062','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00006','','A1',2,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00052','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00059','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00013','','A1',8,1)
insert into table_bom1 values('10-W03A000-00004->','30-0040000-00001','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','18-0300000-00003','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','20-0100000-00004','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','19-0100001-00004','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','17-0100000-00027','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','17-0100000-00063','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','13-0100000-00002','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','16-0100000-00001','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00075','','A1',6,1)
insert into table_bom1 values('10-W03A000-00004->','12-0100000-00011','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00011','','A1',3,1)
insert into table_bom1 values('10-W03A000-00004->','14-0100000-00086','','A1',3,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00011','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00012','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00013','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','15-0100000-00095','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','16-0400000-00004','','A1',2,1)
insert into table_bom1 values('10-W03A000-00004->','17-0100000-00004','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','17-0100000-00009','','A1',1,1)
insert into table_bom1 values('10-W03A000-00004->','21-0100000-00021','','A1',1,1)
insert into table_bom1 values('10-W03B000-00001->','12-0600000-00012','','A1',1,1)
insert into table_bom1 values('10-W03B000-00001->','18-0100000-00018','','A1',1,1)
insert into table_bom1 values('10-W03B000-00001->','10-W03A000-00004','10-W03A000-00004->','D',1,1)



父子节点的遍历,可参考: http://www.cnblogs.com/seusoftware/p/3269514.html



我的51CTO博客

SQL Server MCITP, MVP, MCT, DBA
‹‹ 上一贴:SQL Server 群集问题,向各位大神求解,谢谢! ...   |   下一贴:有办法优化查询吗? ››
  发新话题
快速回复主题
关于我们 | 诚聘英才 | 联系我们 | 网站大事 | 友情链接 |意见反馈 | 网站地图
Copyright©2005-2018 51CTO.COM
本论坛言论纯属发布者个人意见,不代表51CTO网站立场!如有疑义,请与管理员联系:bbs@51cto.com