--TRY
WITH BOM(Father,Code, Quantity,iLevel )
AS
(
SELECT CONVERT(NVARCHAR(40), T0.CODE) Father ,CONVERT(NVARCHAR(40), T1.Code ) ,CONVERT(NUMERIC(19,6),T1.Quantity/T0.Qauntity) Quantity, 1 iLevel FROM OITT T0
JOIN ITT1 T1 ON T0.Code = T1.Father
UNION ALL
SELECT CONVERT(NVARCHAR(40), T0.Father) ,CONVERT(NVARCHAR(40),T1.CODE) , CONVERT(NUMERIC(19,6),T2.Quantity/T1.Qauntity*T0.Quantity) , T0.iLevel + 1 iLevel
FROM BOM T0
JOIN OITT T1 ON T0.Code = T1.Code JOIN ITT1 T2 ON T1.Code = T2.Father
JOIN (SELECT MAX(iLevel) AS iLevel FROM BOM T3) AS T ON T0.iLevel =T.iLevel
)
SELECT * FROM BOM
WITH BOM(Father,Code, Quantity,iLevel )
AS
(
SELECT CONVERT(NVARCHAR(40), T0.CODE) Father ,CONVERT(NVARCHAR(40), T1.Code ) ,CONVERT(NUMERIC(19,6),T1.Quantity/T0.Qauntity) Quantity, 1 iLevel FROM OITT T0
JOIN ITT1 T1 ON T0.Code = T1.Father
UNION ALL
SELECT CONVERT(NVARCHAR(40), T0.Father) ,CONVERT(NVARCHAR(40),T1.CODE) , CONVERT(NUMERIC(19,6),T2.Quantity/T1.Qauntity*T0.Quantity) , T0.iLevel + 1 iLevel
FROM BOM T0
JOIN OITT T1 ON T0.Code = T1.Code JOIN ITT1 T2 ON T1.Code = T2.Father
JOIN (SELECT MAX(iLevel) AS iLevel FROM BOM T3) AS T ON T0.iLevel =T.iLevel
)
SELECT * FROM BOM
WITH BOM(Father,Code, Quantity,iLevel )
AS
(
SELECT CONVERT(NVARCHAR(40), T0.CODE) Father ,CONVERT(NVARCHAR(40), T1.Code ) ,CONVERT(NUMERIC(19,6),T1.Quantity/T0.Qauntity) Quantity, 1 iLevel FROM OITT T0
JOIN ITT1 T1 ON T0.Code = T1.Father
UNION ALL
SELECT CONVERT(NVARCHAR(40), T0.Father) ,CONVERT(NVARCHAR(40),T1.CODE) , CONVERT(NUMERIC(19,6),T2.Quantity/T1.Qauntity*T0.Quantity) , T0.iLevel + 1 iLevel
FROM BOM T0
JOIN OITT T1 ON T0.Code = T1.Code JOIN ITT1 T2 ON T1.Code = T2.Father
-- JOIN (SELECT MAX(iLevel) AS iLevel FROM BOM T3) AS T ON T0.iLevel =T.iLevel
)
SELECT * FROM BOM
AS
(
SELECT CONVERT(NVARCHAR(40), T0.CODE) Father ,CONVERT(NVARCHAR(40), T1.Code ) ,CONVERT(NUMERIC(19,6),T1.Quantity/T0.Qauntity) Quantity, 1 iLevel FROM OITT T0
JOIN ITT1 T1 ON T0.Code = T1.Father
UNION ALL
SELECT CONVERT(NVARCHAR(40), T0.Father) ,CONVERT(NVARCHAR(40),T1.CODE) , CONVERT(NUMERIC(19,6),T2.Quantity/T1.Qauntity*T0.Quantity) , T0.iLevel + 1 iLevel
FROM BOM T0
JOIN OITT T1 ON T0.Code = T1.Code --AND T0.iLevel = (SELECT MAX(iLevel) FROM BOM T3) JOIN ITT1 T2 ON T1.Code = T2.Father
)
SELECT * FROM BOM --错误是不是返回无数条记录--下面事倒或许对你有帮助:CREATE TABLE dbo.Flights
(
city1 NVARCHAR(256) NOT NULL ,
city2 NVARCHAR(256) NOT NULL ,
distance INT NOT NULL,
PRIMARY KEY(city1, city2),
CHECK(city1 < city2),
CHECK(distance > 0)
);insert into flights values('BeiJing', 'TaiYuan', 10 )
insert into flights values('Beijing','ZhenZhou' ,15)
insert into flights values('TaiYuan', 'XiAn' ,12 )
insert into flights values('TaiYuan' ,'YingChuan' ,14 )
insert into flights values('TaiYuan' ,'ZhenZhou' ,8 )
insert into flights values('WuHan', 'XiAn' ,13 )
insert into flights values('WuHan' ,'ZhenZhou' ,10 )
insert into flights values('XiAn' ,'YingChuan' ,13 )
insert into flights values('XiAn', 'ZhenZhou', 8 )
insert into flights values('yingchuan', 'wuhan', 20 )select * from flights
*/declare @city1 varchar (100);
declare @city2 varchar (100);
set @city1='beijing';
set @city2='wuhan';
with AllFlights
as
(
select city1,city2,distance from flights
union
select city2,city1,distance from flights
),
CompleteFlights as
(
select city1,city2,distance ,cast(city1+'.' as nvarchar(1024)) as [path]
from AllFlights
union all
select a.city1,b.city2,a.distance+b.distance,cast ([path]+a.city2+'.' as nvarchar(1024))
from CompleteFlights a join AllFlights b
on a.city2=b.city1 and charindex(b.city1,[path])=0 and charindex(b.city2,[path])=0
)
select * from CompleteFlights
where city1=@city1 and city2=@city2 --order by distance
and distance= (select min(distance) from CompleteFlights where city1=@city1 and city2=@city2)