集群架构是什么呢？
看起来就是执行sql慢，被放进了慢线程池，后边就没有反应了。
sql：
SELECT TOP 200 "组织", "年度", "期间", "成本域", "销售类型二级"
, "料品编码", "料品名称", "规格", "入库数量", "转换率"
, "单位材料", "单位包材", "主料", "辅料", "半成品"
, "材料成本合计", "包材", "采购单价单位材料", "采购单价单位包材", "材料采购库存单价"
, "包材采购库存单价"
FROM (select t13.组织 AS 组织,t13.年度 as 年度,t13.期间 as 期间,t13.成本域 as 成本域,t13.销售类型二级 as 销售类型二级,t13.料品编码 as 料品编码,t13.料品名称 as 料品名称,t13.规格 as 规格,t13.入库数量 as 入库数量,t13.转换率 as 转换率,单位材料 as 单位材料,单位包材 as 单位包材,主料 as 主料,辅料 as 辅料,半成品 as 半成品,材料成本合计 as 材料成本合计,包材 as 包材,采购单价单位材料 as 采购单价单位材料,采购单价单位包材 as 采购单价单位包材,材料采购库存单价 as 材料采购库存单价,包材采购库存单价 as 包材采购库存单价
from (select t12.组织,t12.年度,t12.期间,t12.成本域,t12.销售类型二级,t12.料品编码,t12.料品名称,t12.规格,t12.入库数量,t12.转换率,
单位材料,单位包材,t12.物料数量,t12.最近采购时间,t12.最近采购单价,t12.物料编码,主料,辅料,半成品,材料成本合计,包材,采购单价单位材料,采购单价单位包材,t12.最近采购数量,
(ISNULL(t12.采购单价单位材料,0)+ISNULL(t12.期初主料价,0)+ISNULL(t12.期初辅料价,0)+ISNULL(t12.期初半成品价,0)) as 材料采购库存单价,(ISNULL(t12.采购单价单位包材,0)+ISNULL(t12.期初包材价,0)) as 包材采购库存单价
from (select *,sum(t11.期初主料m) over (partition by t11.期间,t11.料品编码,t11.组织,t11.成本域) as 期初主料价,
sum(t11.期初辅料m) over (partition by t11.期间,t11.料品编码,t11.组织,t11.成本域) as 期初辅料价,
sum(t11.期初半成品m) over (partition by t11.期间,t11.料品编码,t11.组织,t11.成本域) as 期初半成品价,
sum(t11.期初包材m) over (partition by t11.期间,t11.料品编码,t11.组织,t11.成本域) as 期初包材价
from(select *,
case when t10.期初主料单价 = 'NULL' THEN NULL else CAST(t10.期初主料单价 AS numeric(38,6)) end as 期初主料m,
case when t10.期初辅料单价 = 'NULL' THEN NULL else CAST(t10.期初辅料单价 AS numeric(38,6)) end as 期初辅料m,
case when t10.期初半成品单价 = 'NULL' THEN NULL else CAST(t10.期初半成品单价 AS numeric(38,6)) end as 期初半成品m,
case when t10.期初包材单价 = 'NULL' THEN NULL else CAST(t10.期初包材单价 AS numeric(38,6)) end as 期初包材m
from (select *,(case when substring(期初料号,1,4) = '1001' then convert(varchar,库存单价,120) else 'NULL' end ) as 期初主料单价,
(case when substring(期初料号,1,4) = '1002' then convert(varchar,库存单价,120) else 'NULL' end ) as 期初辅料单价,
(case when substring(期初料号,1,3) = '300' then convert(varchar,库存单价,120) else 'NULL' end ) as 期初半成品单价,
(case when substring(期初料号,1,4) = '1003' or substring(期初料号,1,4) = '0103' then convert(varchar,库存单价,120) else 'NULL' end ) as 期初包材单价
from (select *,t8.期初金额/t8.总数量 as 库存单价
from (select *,a3.期初数量 + t7.最近采购数量 as 总数量
from(select
t6.组织,t6.年度,t6.期间,t6.成本域,t6.销售类型二级,t6.料品编码,t6.料品名称,t6.规格,t6.入库数量,t6.转换率,
(ISNULL(t6.半成品,0)+ISNULL(t6.辅料,0)+ISNULL(t6.主料,0)) / t6.入库数量 as 单位材料,
ISNULL(t6.包材,0)/ t6.入库数量 as 单位包材,
t6.物料数量,t6.最近采购时间,t6.最近采购单价,t6.物料编码,主料,辅料,半成品,
ISNULL(t6.半成品,0)+ISNULL(t6.辅料,0)+ISNULL(t6.主料,0) as 材料成本合计,
包材,采购单价单位材料,采购单价单位包材,t6.最近采购数量
from(select t5.组织,t5.年度,t5.期间,t5.成本域,t5.销售类型二级,t5.料品编码,t5.料品名称,t5.规格,t5.入库数量,t5.转换率,t5.物料数量,t5.最近采购时间,t5.最近采购单价,t5.物料编码,t5.最近采购数量,
sum(t5.主料m) over (partition by t5.期间,t5.料品编码,t5.组织,t5.成本域) as 主料,
sum(t5.辅料m) over (partition by t5.期间,t5.料品编码,t5.组织,t5.成本域) as 辅料,
sum(t5.半成品m) over (partition by t5.期间,t5.料品编码,t5.组织,t5.成本域) as 半成品,
sum(t5.包材m) over (partition by t5.期间,t5.料品编码,t5.组织,t5.成本域) as 包材,
sum(ISNULL(t5.主料s,0) * ISNULL(t5.最近采购单价,0) + ISNULL(t5.辅料s,0) * ISNULL(t5.最近采购单价,0) + ISNULL(t5.半成品s,0) * ISNULL(t5.最近采购单价,0) + ISNULL(t5.包材s,0) * ISNULL(t5.最近采购单价,0)) over (partition by t5.期间,t5.料品编码,t5.组织,t5.成本域) / t5.入库数量 as 采购单价单位材料,
sum(ISNULL(t5.包材s,0) * ISNULL(t5.最近采购单价,0) ) over (partition by t5.期间,t5.料品编码,t5.组织,t5.成本域) / t5.入库数量 as 采购单价单位包材
from (select * ,
sum(t3.主料m) over (partition by t3.期间,t3.料品编码,t3.组织,t3.成本域,t3.物料编码) as 主料s,
sum(t3.辅料m) over (partition by t3.期间,t3.料品编码,t3.组织,t3.成本域,t3.物料编码) as 辅料s,
sum(t3.半成品m) over (partition by t3.期间,t3.料品编码,t3.组织,t3.成本域,t3.物料编码) as 半成品s,
sum(t3.包材m) over (partition by t3.期间,t3.料品编码,t3.组织,t3.成本域,t3.物料编码) as 包材s
from (select t1.期间,
t1.年度,t1.成本域,t1.销售类型二级,t1.料品编码,t1.料品名称,t1.规格,t1.入库数量,t1.物料数量,t1.物料编码,
case when t1.主料n = 'NULL' THEN NULL else CAST(t1.主料n AS numeric(38,6)) end as 主料m,
case when t1.半成品n = 'NULL' THEN NULL else CAST(t1.半成品n AS numeric(38,6)) end as 半成品m,
case when t1.辅料n = 'NULL' THEN NULL else CAST(t1.辅料n AS numeric(38,6)) end as 辅料m,
case when t1.包材n = 'NULL' THEN NULL else CAST(t1.包材n AS numeric(38,6)) end as 包材m,
t2.*
from (select qj as 期间,substring(qj,1,4) as 年度,depname as 成本域,ParentItemCategoryName as 销售类型二级,
ParentItemCode as 料品编码,ParentItemName as 料品名称,ParentItemSPECS as 规格,parentitemrcvqty as 入库数量,issueqty as 物料数量,ComponentItemcode as 物料编码,
(case when substring(ComponentItemcode,1,4) = '1001' then convert(varchar,issueqty,120) else 'NULL' end ) as 主料n,
(case when substring(ComponentItemcode,1,4) = '1002' then convert(varchar,issueqty,120) else 'NULL' end ) as 辅料n,
(case when substring(ComponentItemcode,1,3) = '300' then convert(varchar,issueqty,120) else 'NULL' end ) as 半成品n,
(case when substring(ComponentItemcode,1,4) = '1003' or substring(ComponentItemcode,1,4) = '0103' then convert(varchar,issueqty,120) else 'NULL' end ) as 包材n
from dbo.fact_U9_CPDL_hz ) t1
left join
(select 组织,记账期间,部门,库存数量,成本数量,金额,单价,料号 as 物料料号,转换率
from dbo.U9_schlb) t2 on t1.期间 = t2.记账期间 and t1.物料编码 = t2.物料料号 and t1.成本域 = t2.部门) t3
left join (select max(substring(convert(varchar,最近采购日期,120),1,7)) as 最近采购时间,料品名称 as 物料名称,料品编号,max(最近一次采购价) as 最近采购单价,max(数量) as 最近采购数量
from dbo.U9_zjyccgj group by 料品编号,料品名称 ,规格,库存单位
) t4 on t3.物料编码 = t4.料品编号)t5)t6)t7
left join
(select a1.期初 as 期初时间,a1.料号 as 期初料号,a1.部门 as 期初部门,a2.库存数量 as 期初数量,a2.金额 as 期初金额
from (select min(记账期间) as 期初,料号,部门
from dbo.U9_schlb group by 料号,部门) a1
join (select *
from dbo.U9_schlb
)a2 on a1.料号 = a2.料号 and a1.期初 = a2.记账期间 and a1.部门= a2.部门) a3 on t7.物料编码 = a3.期初料号 and t7.成本域 = a3.期初部门) t8) t9) t10)t11)t12) t13 group by t13.组织,t13.年度,t13.期间,t13.成本域,t13.销售类型二级,t13.料品编码,t13.料品名称,t13.规格,t13.入库数量,t13.转换率,单位材料,单位包材,主料,辅料,半成品,材料成本合计,包材,采购单价单位材料,采购单价单位包材,材料采购库存单价,包材采购库存单价
) "SUB_QRY"] |
显示全部楼层
发表于 2023-9-15 19:34:02
