SELECT A.父编码, IIf(IsNull([C].[子编码]),IIf(IsNull([B].[子编码]),[A].[层级],[B].[层级]),[C].[层级]) AS 层, IIf(IsNull([C].[子编码]),IIf(IsNull([B].[子编码]),[A].[子编码],[B].[子编码]),[C].[子编码]) AS 码, IIf(IsNull(A.用量),1,A.用量)*IIf(IsNull(B.用量),1,B.用量)*IIf(IsNull(C.用量),1,C.用量) AS 量
FROM (Bom明细表 AS A LEFT JOIN Bom明细表 AS B ON A.子编码=B.父编码) LEFT JOIN Bom明细表 AS C ON B.子编码=C.父编码;