34,838
社区成员




;WITH cte AS
(
SELECT a.[料号],a.[是否替代料],a.[料号] AS [原料号],[呆料数量]=ISNULL(b.[呆料数量],0) FROM [ta] AS a LEFT JOIN [呆料表] b ON a.[料号]=b.[料号]
WHERE a.[原料号]='1'
UNION ALL
SELECT a.[料号],a.[是否替代料],a.[原料号],[呆料数量]=ISNULL(b.[呆料数量],0) FROM [ta] AS a
INNER JOIN [呆料表] b ON a.[料号]=b.[料号]
INNER JOIN cte AS t ON t.[料号]=a.[原料号]
)
SELECT [料号],[需求数],[申购数]=CASE WHEN [呆料数量]>=[需求数] THEN 0 ELSE [需求数]-[呆料数量] END
FROM [tb] AS a,
(
SELECT [原料号],SUM([呆料数量]) AS [呆料数量] FROM cte GROUP BY [原料号]
) AS b
WHERE a.[料号]=b.[原料号]
BOM结构(已生成,这里是替代部分)
料号 是否替代料 原料号 用量
A 否 1
A1 是 A
A2 是 A
......
B 否 1
B1 是 B
AA 否 1
A1 是 AA
......
呆料表
料号 呆料数量
A 100
A1 5000
A2 3000
B1 2000
现要求生产T6500套,求物料申购数
(申购数是需求数减去可以替代的数量)
料号 需求数 申购数
A 6500 0 (6500-A_100-A1_5000-A2_1400-->A2余1600)
B 6500 4500 (6500-B1_2000)
AA 6500 4900 (6500-A2余_1600)
create table #EnterPrise
(
Department nvarchar(50),--部门名称
ParentDept nvarchar(50),--上级部门
DepartManage nvarchar(30)--部门经理
)
insert into #EnterPrise select '技术部','总经办','Tom'
insert into #EnterPrise select '商务部','总经办','Jeffry'
insert into #EnterPrise select '商务一部','商务部','ViVi'
insert into #EnterPrise select '商务二部','商务部','Peter'
insert into #EnterPrise select '程序组','技术部','GiGi'
insert into #EnterPrise select '设计组','技术部','yoyo'
insert into #EnterPrise select '专项组','程序组','Yue'
insert into #EnterPrise select '总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;with hgo as
(
select *,0 as rank from #EnterPrise where DepartManage='Tom'
union all
select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo
/*
Department ParentDept DepartManage rank
--------------- -------------------- ----------------------- -----------
技术部 总经办 Tom 0
程序组 技术部 GiGi 1
设计组 技术部 yoyo 1
专项组 程序组 Yue 2
*/
--查询部门经理是GiGi的上级部门名称
;with hgo as
(
select *,0 as rank from #EnterPrise where DepartManage='GiGi'
union all
select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo
/*
Department ParentDept DepartManage rank
-------------------- ---------------------- ----------- -----------
程序组 技术部 GiGi 0
技术部 总经办 Tom 1
总经办 Boss 2
*/