想了1天还是没想到这个语句怎么写,有谁能帮我看下吗



这个是对应的BOM 表. 我将像里面的数据都取出来.
下面的是数据库里面的数据.
我想将对应的 合-25.400X1140.00 其中的类型为0的取出来,
想了好长时间 不知道咋写,请大家帮忙给个思路.

[解决办法]
递归啊
with ta as
(select * from BOM表
where 母件= ‘合-25.400X1140.00’and 类型=0
union all
select * from ta a
inner join BOM表 b on a.子键=b.目件
)
select * from ta
类型为0你自己加，
[解决办法]

create table BOM(母件 nvarchar(100),子件 nvarchar(100),类型 int)
insert into BOM values('合-25.400X1140.00','S45C-15.400X1140.00',1)
insert into BOM values('合-25.400X1140.00','SCM420-25.400X045.500',1)
insert into BOM values('合-25.400X1140.00','SS41-10.200X030.700',0)
insert into BOM values('合-25.400X1140.00','SS41-23.300X011.300',0)
insert into BOM values('合-25.400X1140.00','U01-0001',0)
insert into BOM values('合-25.400X1140.00','U01-0002',0)
insert into BOM values('SCM420-25.400X045.500','E02-0050',0)
insert into BOM values('SCM420-25.400X045.500','E02-0182',0)
insert into BOM values('SCM420-25.400X045.500','E05-0019',0)
insert into BOM values('SCM420-25.400X045.500','E05-0093',0)
insert into BOM values('SCM420-25.400X045.500','E05-0130',0)
insert into BOM values('SCM420-25.400X045.500','E05-0137',0)
insert into BOM values('SCM420-25.400X045.500','E05-0137',0)
insert into BOM values('SCM420-25.400X045.500','E05-0145',0)
insert into BOM values('SCM420-25.400X045.500','E05-0253',0)
insert into BOM values('SCM420-25.400X045.500','E05-0343',0)
insert into BOM values('SCM420-25.400X045.500','SCM420-25.00X6.0M-B',0)

--跟级联查询一样 加载执行下面语句  显示只是母件不是子件
select 母件 from BOM where 母件 not in(select 子件 from BOM) group by 母件
--当点击母件时 显示其下类型为0的子件, 参数为点击的母件
select 子件 
from BOM 
where 母件='合-25.400X1140.00' and 类型=0

if object_id('[BOM]') is not null drop table [BOM]
go 

create table BOM(母件 nvarchar(100),子件 nvarchar(100),类型 int)
insert into BOM values('合-25.400X1140.00','S45C-15.400X1140.00',1)
insert into BOM values('合-25.400X1140.00','SCM420-25.400X045.500',1)
insert into BOM values('合-25.400X1140.00','SS41-10.200X030.700',0)
insert into BOM values('合-25.400X1140.00','SS41-23.300X011.300',0)
insert into BOM values('合-25.400X1140.00','U01-0001',0)
insert into BOM values('合-25.400X1140.00','U01-0002',0)
insert into BOM values('SCM420-25.400X045.500','E02-0050',0)
insert into BOM values('SCM420-25.400X045.500','E02-0182',0)
insert into BOM values('SCM420-25.400X045.500','E05-0019',0)
insert into BOM values('SCM420-25.400X045.500','E05-0093',0)
insert into BOM values('SCM420-25.400X045.500','E05-0130',0)
insert into BOM values('SCM420-25.400X045.500','E05-0137',0)
insert into BOM values('SCM420-25.400X045.500','E05-0137',0)
insert into BOM values('SCM420-25.400X045.500','E05-0145',0)
insert into BOM values('SCM420-25.400X045.500','E05-0253',0)
insert into BOM values('SCM420-25.400X045.500','E05-0343',0)
insert into BOM values('SCM420-25.400X045.500','SCM420-25.00X6.0M-B',0)
go


--1.定义表变量

DECLARE @a nvarchar(100)
SET @a='合-25.400X1140.00'

declare @tb table
(
母件 nvarchar(100),
子件 nvarchar(100),
类型 int,
level int       --层级
)


--2.递归开始  
insert into @tb 
SELECT bom.* ,1 [level]
FROM BOM 
where 母件 = @a



--3.递归的过程
while @@ROWCOUNT > 0
begin
    
    insert into @tb
select b.母件,b.子件,b.类型,level + 1
from @tb t
inner join bom b 
 
on b.母件  =t.子件
   and t.类型 = 1
    where not exists(select 1 from @tb t2 
                     where t.level < t2.level)
end


--4.最后查询
SELECT 母件,子件,类型
 FROM  @tb
 where 类型 = 0
/*
母件子件类型
合-25.400X1140.00SS41-10.200X030.7000
合-25.400X1140.00SS41-23.300X011.3000
合-25.400X1140.00U01-00010
合-25.400X1140.00U01-00020
SCM420-25.400X045.500E02-00500
SCM420-25.400X045.500E02-01820
SCM420-25.400X045.500E05-00190
SCM420-25.400X045.500E05-00930
SCM420-25.400X045.500E05-01300
SCM420-25.400X045.500E05-01370
SCM420-25.400X045.500E05-01370
SCM420-25.400X045.500E05-01450
SCM420-25.400X045.500E05-02530
SCM420-25.400X045.500E05-03430
SCM420-25.400X045.500SCM420-25.00X6.0M-B0
*/