一个算法的问题，sql语句
表1：ICBOMChild(FInterID,FEntryID,FItemID,FUnitID,FQty) 这里的FItemID会重复
表2：ICBOM(FInterID,FBOMNumber,FParentID,FItemID,FUnitID,FQty) 这里的ICBOMChild.FInterID=ICBOM.FInterID，2个表都是唯一的。
表3：t_MeasureUnit(FMeasureID,FName) t_MeasureUnit.FMeasureID=ICBOMChild.FUuitID
t_MeasureUnit.FMeasureID=ICBOM.FUuitID （这个容易）
表4：t_ICItem(FItemID,FNumber,FName) t_ICItem的FItemID唯一，且对应一个FNumber，还有物料名称 t_ICItem.FItemID=ICBOM.FItemID,t_ICItem.FNumber
=ICBOM.FBOMNumber
这里表2相当于主表，从他开始，表1是表2的一个分录表，就是具体组成的部分。我下面给出一个语句，就是一级的一个清单，相信大家看了就明白了。
现在由于我给的语句中，物料子代码可能=物料代码，故还要继续向下分级（比如电脑由显示器和键盘，鼠标，主机组成，主机由主板，DVD，CPU，硬盘组成，DVD光驱由外壳和电线组成，电线由绝缘壳和铜丝组成。现在我是分开一个一个写的，就一级，现在我要能连起来的都连起来的，有的比如电线可能在多处都要用到，故不先不考虑删除），一直向下分级；当也存在有的向下分级就只有1级，有的有20多级。能向下分级的要分级，不能的就可以用null。因为不知道要多少级，有人能写吗。
[解决办法]
IF OBJECT_ID('t_IC_Item')is not null
drop table t_IC_Item
IF OBJECT_ID('t_Unit')is not null
drop table t_Unit
IF OBJECT_ID('t_BOM')is not null
drop table t_BOM
IF OBJECT_ID('t_BOMChild')is not null
drop table t_BOMChild
CREATE TABLE t_IC_Item(FItemID int,FNumber varchar(200),FName varchar(200),FModel varchar(200),FUnitID int,FErpClsID int)
INSERT t_IC_Item
SELECT 1,'CP.001','产品1' ,'产品规格1' ,1,2 UNION
SELECT 2,'BC.001','半成品1' ,'半成品规格1' ,1,2 UNION
SELECT 3,'BC.002','半成品2' ,'半成品规格2' ,1,2 UNION
SELECT 4,'BC.003','半成品3' ,'半成品规格3' ,1,3 UNION
SELECT 5,'BC.004','半成品4' ,'半成品规格4' ,1,2 UNION
SELECT 6,'ZC.001','主材料1' ,'主材料规格1' ,1,1 UNION
SELECT 7,'ZC.002','主材料2' ,'主材料规格2' ,1,1 UNION
SELECT 8,'ZC.003','主材料3' ,'主材料规格3' ,1,1 UNION
SELECT 9,'ZC.004','主材料4' ,'主材料规格4' ,1,1 UNION
SELECT 10,'ZC.005','主材料5' ,'主材料规格5' ,1,1 UNION
SELECT 11,'ZC.006','主材料6' ,'主材料规格6' ,1,1 UNION
SELECT 12,'ZC.007','主材料7' ,'主材料规格7' ,1,1 UNION
SELECT 13,'ZC.008','主材料8' ,'主材料规格8' ,1,1 UNION
SELECT 14,'ZC.009','主材料9' ,'主材料规格9' ,1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1
CREATE TABLE t_Unit(FID int,FName varchar(20))
INSERT t_Unit SELECT 1,'个'
CREATE TABLE t_BOM(FID int,FBomNo varchar(200),FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOM
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 UNION
SELECT 5,'BOM05',5,1,1
CREATE TABLE t_BOMChild(FID int,FEntryID int,FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOMChild
SELECT 1,1, 2,1,1 UNION
SELECT 1,2, 3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1 UNION
SELECT 2,1, 6,1,1 UNION
SELECT 2,2, 7,1,1 UNION
SELECT 3,1, 8,1,1 UNION
SELECT 3,2, 9,1,1 UNION
SELECT 3,3, 7,1,1 UNION
SELECT 3,4, 4,1,1 UNION
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3, 5,1,1 /*UNION
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5, 7,1,1*/
GO
with cet as(
select distinct 0 Level,cast(right('000'+rtrim(a.FItemID),4) AS varchar(20)) Code,a.FItemID,a.FNumber,
a.FName,a.FModel,a.FErpClsID,cast(b.FQty AS float) FQty,c.FName FName1,cast('正常' as varchar(10)) Status
from t_IC_Item a,t_BOM b,t_Unit c
where
a.FUnitID=b.FUnitID and b.FUnitID=c.FID
and not exists(select 1 from t_BOM d,t_BOMChild e where d.FID=e.FID and e.FItemID=a.FItemID)
and exists(select 1 from t_BOM m,t_BOMChild n where m.FID=n.FID and M.FID=a.FItemID)
union all
select Level + 1,cast(e.Code+right('000'+rtrim(d.FEntryID),4) AS varchar(20)),
a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,cast(b.FQty*d.FQty*e.FQty AS float),c.FName,
cast(case when a.FItemID in(select m.FID from t_BOM m,t_BOMChild n where m.FID=n.FID) then '正常'
when a.FItemID in(select FID from t_BOM) then 'BOM未建'
else ''
end as varchar(10))
from t_IC_Item a,t_BOM b,t_Unit c,t_BOMChild d,cet e
where
a.FUnitID=b.FUnitID
and b.FUnitID=c.FID
and a.FItemID=d.FItemID
and b.FID=d.FID
and d.FID=e.FItemID
-- and e.level=@i-1
)select Level ,FItemID,FName,FModel,
(case FErpClsID when 1 then '外购' when 2 then '便宜'when 3 then '委外加工' else '' end) as FErpClsID,FQty,FName1,Status from cet a order by Code
/*
Level FItemID FName FModel FErpClsID FQty FName1 Status
----------- ----------- ---------------------------- ---------------------------- --------- ---------------------- -------------------- ----------
0 1 产品1 产品规格1 便宜 1 个 正常
1 2 半成品1 半成品规格1 便宜 1 个 正常
2 6 主材料1 主材料规格1 外购 1 个
2 7 主材料2 主材料规格2 外购 1 个
1 3 半成品2 半成品规格2 便宜 1 个 正常
2 8 主材料3 主材料规格3 外购 1 个
2 9 主材料4 主材料规格4 外购 1 个
2 7 主材料2 主材料规格2 外购 1 个
2 4 半成品3 半成品规格3 委外加工 1 个 正常
3 10 主材料5 主材料规格5 外购 1 个
3 11 主材料6 主材料规格6 外购 1 个
3 5 半成品4 半成品规格4 便宜 1 个 BOM未建
1 15 主材料10 主材料规格10 外购 1 个
1 17 主材料12 主材料规格12 外购 1 个
(14 行受影响)
*/