想了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
*/
