取云星空所有有效bom数据

最新推荐文章于 2024-06-14 16:21:11 发布

Lainesun

最新推荐文章于 2024-06-14 16:21:11 发布

阅读量792

点赞数

文章标签：数据库 database

本文链接：https://blog.csdn.net/Lainesun/article/details/125293256

版权

该SQL语句使用CTE（公共表表达式）从数据库中检索最顶级BOM及其最新子项的数据。首先，查询顶层BOM信息，然后通过子查询获取非0层的子项BOM的最新版本。结果按BOM内码和项次组合排序，并且仅包含最新版本的BOM记录。

摘要由CSDN通过智能技术生成

;with cte as
(
–1、定点（Anchor）子查询，用来查询最顶级的产品的BOM的
select
0 as BOM层次,t1.fid as 最顶级BOM内码
,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
,t3.FREPLACEGROUP as 项次,CAST(10000+t3.FREPLACEGROUP AS nvarchar) as 项次组合
,cast(CAST(t1.fid AS nvarchar)+‘-’+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar(max)) as BOM内码和项次组合
,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
,case when FMATERIALTYPE = 1 then ‘标准件’
when FMATERIALTYPE = 2 then ‘返还件’
when FMATERIALTYPE = 3 then ‘替代件’
else ‘未知类型’ end as 子项类型
,t3.FNUMERATOR as 分子,t3.FDENOMINATOR as 分母,t3.FFIXSCRAPQTY as 固定损耗,t3.FSCRAPRATE as 变动损耗,t3.FBOMID,t1.FUSEORGID
,0 as 是否有子项BOM版本
from dbo.T_ENG_BOM t1
join T_BD_MATERIAL fxwl --用父项关联物料表
on fxwl.FMATERIALID = t1.FMATERIALID
and t1.FFORBIDSTATUS = ‘A’ --只取未禁用状态的BOM
join T_BD