DECLARE @partid INT
SELECT @partid = 24796;
WITH parts ( pbom, pid, cbom, cid, lvl, seq, sortseq )
AS ( SELECT NULL ,
p.ParentId ,
p.BomId ,
p.ParentId ,
0 ,
0 ,
0
FROM dbo.bom_parent AS p
JOIN dbo.bom_bom AS b ON b.BomId = p.BomId
WHERE b.VersionEffDate <= GETDATE()
AND b.VersionEndDate > GETDATE()
AND b.BomType = 1
and ParentId=@partid
UNION
SELECT p.BomId ,
p.ParentId ,
cb.BomId ,
c.ComponentId ,
0 AS lvl ,
CAST(ROW_NUMBER() OVER ( PARTITION BY c.BomId ORDER BY c.SortSeq ) AS INT) AS seq ,
c.SortSeq
FROM dbo.bom_parent AS p
JOIN dbo.bom_bom AS b ON b.BomId = p.BomId
JOIN dbo.bom_opcomponent AS c ON c.BomId = p.BomId
LEFT JOIN dbo.bom_parent AS cb ON c.ComponentId = cb.ParentId
LEFT JOIN dbo.bom_bom AS bc ON bc.BomId = cb.BomId
--LEFT JOIN dbo.bom_opcomponentopt bo ON c.OptionsId = bo.OptionsId
WHERE b.VersionEffDate <= GETDATE()
AND b.VersionEndDate > GETDATE() and p.ParentId=@partid
AND ( ( bc.VersionEffDate <= GETDATE()
AND bc.VersionEndDate > GETDATE()
AND bc.BomType = 1
)
OR bc.BomId IS NULL
)
),
bom
AS ( SELECT parts.pid AS ancestor ,
parts.pbom ,
parts.pid ,
parts.cbom ,
parts.cid ,
parts.lvl ,
parts.seq ,
parts.sortseq ,
CAST('01' AS NVARCHAR(50)) AS pseq
FROM parts
WHERE parts.pbom IS NULL
AND parts.pid = @partid
UNION ALL
SELECT p.ancestor ,
c.pbom ,
c.pid ,
c.cbom ,
c.cid ,
p.lvl + 1 ,
c.seq ,
c.sortseq ,
CAST(p.pseq + '.' + RIGHT('00' + CAST(c.seq AS NVARCHAR(50)), 2) AS NVARCHAR(50))
FROM parts AS c
JOIN bom AS p ON p.cbom = c.pbom
)
SELECT *
FROM bom AS b
ORDER BY b.pseq
OPTION ( HASH JOIN, MAXRECURSION 10 );
技术类别
技术推荐
人气排行
- 12-31 1C# 二次开发单据按钮 获取表体或者表头信息
- 06-13 2用友U8登录控件的使用
- 12-11 3用友U8生产模块单据API事件的配置
- 11-12 4U8展开BOM的SQL
- 12-11 5谷歌开源移动端3D目标检测库，实时检测目标位置、方向及大小
- 12-11 6.NET开源工程推荐（Accord，AForge，Emgu CV）
- 12-11 7Modbus通讯协议学习 - 认识篇
- 12-11 8.NET高性能编程 - C#如何安全、高效地玩转任何种类的内存之Span的本质(一)
- 12-15 9.NET高性能编程 - C#如何安全、高效地玩转任何种类的内存之Memory(三)
- 01-20 10C++ 解析Json——jsoncpp