系列文章
C#底层库--MySQL脚本自动构建类（insert、update语句生成）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129179216
C#底层库--MySQL数据库访问操作辅助类（推荐阅读）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126886379
C#底层库--SQLiteHelper访问操作辅助类
本文链接：https://blog.csdn.net/youcheng_ge/article/details/123666958
提高编程效率--数据导入工具
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126427323
Oracle数据库限制ip访问
本文链接：https://blog.csdn.net/youcheng_ge/article/details/122220930
SQL 获取数据库表，指定字段并且判断是否为主键
本文链接：https://blog.csdn.net/youcheng_ge/article/details/110820405
SQL outer apply的用法
本文链接：https://blog.csdn.net/youcheng_ge/article/details/79903489
MySQL安装教程（详细）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126037520
MySQL卸载教程（详细）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129279265
MySQL分类汇总(group by...with rollup)，如何显示“总计”字段?
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128217837
MySQL WITH CHECK OPTION的用法
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128147196
MySQL 使用存储过程插入千万级数据如何提升效率？
本文链接：https://blog.csdn.net/youcheng_ge/article/details/77728189
MySQL 数据库表行列转置的实现
本文链接：https://blog.csdn.net/youcheng_ge/article/details/77625052
目录
前言
本专栏为【数据库】，主要介绍SQL的功能与特点、SQL数据定义语言（表、视图、索引、约束）、SQL数据操作语言（数据检索、数据插入、数据删除、数据更新）、创建与删除触发器、SQL数据控制语言（安全性和授权、事务处理）以及嵌入式SQL。
如果你对本专辑感兴趣，持续关注吧。大家有任何问题，也可以评论区反馈，私信我。
一、技术介绍
outer apply添加外部行
outer apply返回改行，并且改行的右表表达式的属性为null。
二、测试用例
IF EXISTS (SELECT * FROM sysobjects WHERE name ='P201_ProductStockRemainFlowQry' and xtype='P')
DROP PROCEDURE [dbo].[P201_ProductStockRemainFlowQry]
go
/*
成品库存流转查询报表取数
exec P201_ProductStockRemainFlowQry @strSysNo='447',@strProductNameCn='',@dtEndDate='2018-3-20'
*/
CREATE Proc P201_ProductStockRemainFlowQry(@strSysNo varchar(20),@strProductNameCn varchar(100),
@dtEndDate datetime)
as
Begin
print '1:' + convert(varchar(40),getdate(), 121)
--取订单(修理包产品)
Create table #tbOrderDetail
(so_product_no varchar(40),product_no varchar(40),order_num float,destine_num float,delivery_num float,
undelivery_num float,delivery_date datetime,product_name_cn varchar(100),ug_product_no varchar(40),
ug_product_name_cn varchar(100),sale_order_guid varchar(40))
Insert Into #tbOrderDetail
(so_product_no,product_no,order_num,destine_num,delivery_num,undelivery_num,
delivery_date,product_name_cn,ug_product_no,ug_product_name_cn,sale_order_guid)
select sod.product_no,rkp.output_product_no1 as product_no,
sod.order_num*rkp.output_material_rate1 as order_num,
sod.destine_num*rkp.output_material_rate1 as destine_num,
sod.delivery_num*rkp.output_material_rate1 as delivery_num,
(case when sod.order_num-sod.delivery_num>0 then sod.order_num-sod.delivery_num
else 0 end)*rkp.output_material_rate1 as undelivery_num,
sod.delivery_date,
isnull(ocProduct.object_name_cn,'') as product_name_cn,
'' as ug_product_no,
'' as ug_product_name_cn,
sod.sale_order_guid
from T201_sale_order_detail sod
inner Join T200_repaire_kit_product rkp on rkp.product_no=sod.product_no and rkp.output_product_no1>''
Left Join T200_object_code ocProduct on ocProduct.object_no=rkp.output_product_no1
where sod.sys_no=@strSysNo
and sod.audit_tag = 'T'
and sod.state_type='run'
and ocProduct.object_name_cn like '%'+@strProductNameCn+'%'
and sod.delivery_date<=@dtEndDate
and exists(select top 1 1 from T200_repaire_kit_product
where product_no=sod.product_no)
Insert Into #tbOrderDetail
(so_product_no,product_no,order_num,destine_num,delivery_num,undelivery_num,
delivery_date,product_name_cn,ug_product_no,ug_product_name_cn,sale_order_guid)
select sod.product_no as so_product_no,rkp.output_product_no2 as product_no,
sod.order_num*rkp.output_material_rate2 as order_num,
sod.destine_num*rkp.output_material_rate2 as destine_num,
sod.delivery_num*rkp.output_material_rate2 as delivery_num,
(case when sod.order_num-sod.delivery_num>0 then sod.order_num-sod.delivery_num
else 0 end)*rkp.output_material_rate2 as undelivery_num,
sod.delivery_date,
isnull(ocProduct.object_name_cn,'') as product_name_cn,
'' as ug_product_no,
'' as ug_product_name_cn,
sod.sale_order_guid
from T201_sale_order_detail sod
inner Join T200_repaire_kit_product rkp on rkp.product_no=sod.product_no and rkp.output_product_no2>''
Left Join T200_object_code ocProduct on ocProduct.object_no=rkp.output_product_no2
where ocProduct.object_name_cn like '%'+@strProductNameCn+'%'
and sod.delivery_date<=@dtEndDate
and exists(select top 1 1 from T200_repaire_kit_product
where product_no=sod.product_no)
--取订单(非修理包产品)
Insert Into #tbOrderDetail
(so_product_no,product_no,order_num,destine_num,delivery_num,undelivery_num,
delivery_date,product_name_cn,ug_product_no,ug_product_name_cn,sale_order_guid)
select sod.product_no as so_product_no,sod.product_no,
sod.order_num,
sod.destine_num,
sod.delivery_num,
case when sod.order_num-sod.delivery_num>0 then sod.order_num-sod.delivery_num
else 0 end as undelivery_num,
sod.delivery_date,
isnull(ocProduct.object_name_cn,'') as product_name_cn,
'' as ug_product_no,
'' as ug_product_name_cn,
sod.sale_order_guid
from T201_sale_order_detail sod
Left Join T200_object_code ocProduct on ocProduct.object_no=sod.product_no
where ocProduct.object_name_cn like '%'+@strProductNameCn+'%'
and sod.delivery_date<=@dtEndDate
and not exists(select top 1 1 from T200_repaire_kit_product
where product_no=sod.product_no)
print '2:' + convert(varchar(40),getdate(), 121)
--更新ug品名
--取UG品的产品类别编码
declare @syspar_type_no_of_UG varchar(20)
select @syspar_type_no_of_UG = dbo.F100_GetStrSysParValue(replace(db_name(),'wsbase',''),'type_no_of_UG')
Declare @tbUgType table(type_no varchar(40))
Insert Into @tbUgType
Select distinct str_value
from dbo.GF_StringSplit(@syspar_type_no_of_UG,';')
where str_value<>''
print '2-1:' + convert(varchar(40),getdate(), 121)
update sod
set sod.ug_product_no=isnull(ppb.material_no,''),
sod.ug_product_name_cn=isnull(ppb.material_name_cn,'')
from #tbOrderDetail sod
outer apply(select top 1 material_no,
isnull(ocMaterial.object_name_cn,'') as material_name_cn
from TA04_product_part_bom ppb
left join T200_product_code pc on pc.product_no=ppb.material_no
left join @tbUgType ut on ut.type_no=pc.type_no
left join T200_object_code ocMaterial on ocMaterial.object_no=ppb.material_no
where ppb.output_product_no=sod.product_no
and ppb.main_tag='T'
and ut.type_no is not null
order by ppb.material_no)ppb
print '3:' + convert(varchar(40),getdate(), 121)
-- 返回结果
Create table #tbResult
(product_no varchar(40),product_name_cn varchar(100),order_num float,destine_num float,delivery_num float,
undelivery_num float,remain_num float,first_delivery_date datetime,last_delivery_date datetime,
ug_product_no varchar(40),ug_product_name_cn varchar(100),ug_remain_num float,ug_plan_num float,
material_no varchar(40),material_name_cn varchar(100),material_rate float,mr_remain_num float,
mjg_num float,rcl_num float,cjg_num float,dz_num float,zt_num float,
product_produce_no varchar(40),prod_product_no varchar(40),last_active_no varchar(40),
cj_product_no varchar(40),dj_product_no varchar(40))
--插入ug品名不为空的记录
Insert Into #tbResult(product_no,product_name_cn,order_num,destine_num,delivery_num,undelivery_num,
remain_num,first_delivery_date,last_delivery_date,ug_product_no,ug_product_name_cn,
ug_remain_num,ug_plan_num,material_no,material_name_cn,material_rate,mr_remain_num,
mjg_num,rcl_num,cjg_num,dz_num,zt_num,product_produce_no,prod_product_no,last_active_no,
cj_product_no,dj_product_no)
select main.product_no,main.product_name_cn,main.order_num,main.destine_num,
main.delivery_num,main.undelivery_num,
isnull(srProduct.remain_num,0) as remain_num,
main.first_delivery_date,main.last_delivery_date,
main.ug_product_no,main.ug_product_name_cn,
isnull(srUgProduct.remain_num,0) as ug_remain_num,
isnull(pd.undelivery_num,0) as ug_plan_num,
isnull(mr.material_no,'') as material_no,
isnull(ocMaterial.object_name_cn,'') as material_name_cn,
isnull(mr.material_rate,0) as material_rate,
main.mr_remain_num,main.mjg_num,main.rcl_num,main.cjg_num,main.dz_num,main.zt_num,
isnull(ug.product_produce_no,'') as product_produce_no,
isnull(ug.product_no,'') as prod_product_no,
'' as last_active_no,
'' as cj_product_no,
'' as dj_product_no
from (select sod.product_no,sod.product_name_cn,
sum(sod.order_num) as order_num,
sum(sod.destine_num) as destine_num,
sum(sod.delivery_num) as delivery_num,
sum(sod.undelivery_num) as undelivery_num,
min(sod.delivery_date) as first_delivery_date,
max(sod.delivery_date) as last_delivery_date,
max(sod.ug_product_no) as ug_product_no,
max(sod.ug_product_name_cn) as ug_product_name_cn,
CAST(0 as float) as mr_remain_num,
CAST(0 as float) as mjg_num,
CAST(0 as float) as rcl_num,
CAST(0 as float) as cjg_num,
CAST(0 as float) as dz_num,
CAST(0 as float) as zt_num
from #tbOrderDetail sod
outer apply(select top 1 product_produce_no,product_no,bom_level
from TA04_product_part_bom
where output_product_no=sod.ug_product_no
and main_tag='T'
order by material_no)ug
where sod.ug_product_no>''
group by sod.product_no,sod.product_name_cn
)main
outer apply(select SUM(remain_num) as remain_num
from T205_stock_remain sr
where sr.product_no=main.product_no)srProduct
outer apply(select SUM(remain_num) as remain_num
from T205_stock_remain sr
where sr.product_no=main.ug_product_no)srUgProduct
outer apply(select SUM(plan_num-delivery_num) as undelivery_num
from TA03_PD_plan_detail pd
where pd.product_no=main.ug_product_no
and state_type='run')pd
outer apply(select top 1 product_no,product_produce_no,material_no,bom_level
from TA04_product_part_bom
where output_product_no=main.product_no
and material_no=main.ug_product_no
and main_tag='T'
order by material_no)ug
outer apply(select ppb.material_no,ppb.material_rate,ppb.bom_level
from TA04_product_part_bom ppb
left join T200_product_code pc on pc.product_no=ppb.material_no
where ppb.output_product_no=ug.material_no
and ppb.product_no=ug.product_no
and ppb.product_produce_no=ug.product_produce_no
and LEN(ppb.bom_level)=LEN(ug.bom_level)+4
and ppb.main_tag='T'
and pc.self_purchase='self_made')mr
Left Join T200_object_code ocMaterial on ocMaterial.object_no=mr.material_no
print '4:' + convert(varchar(40),getdate(), 121)
--插入ug品名为空的记录
Insert Into #tbResult(product_no,product_name_cn,order_num,destine_num,delivery_num,undelivery_num,
remain_num,first_delivery_date,last_delivery_date,ug_product_no,ug_product_name_cn,
ug_remain_num,ug_plan_num,material_no,material_name_cn,material_rate,mr_remain_num,
mjg_num,rcl_num,cjg_num,dz_num,zt_num,product_produce_no,prod_product_no,last_active_no,
cj_product_no,dj_product_no)
select main.product_no,main.product_name_cn,main.order_num,main.destine_num,
main.delivery_num,main.undelivery_num,
isnull(srProduct.remain_num,0) as remain_num,
main.first_delivery_date,main.last_delivery_date,
main.ug_product_no,main.ug_product_name_cn,
isnull(srUgProduct.remain_num,0) as ug_remain_num,
isnull(pd.undelivery_num,0) as ug_plan_num,
isnull(mr.material_no,'') as material_no,
isnull(ocMaterial.object_name_cn,'') as material_name_cn,
isnull(mr.material_rate,0) as material_rate,
main.mr_remain_num,main.mjg_num,main.rcl_num,main.cjg_num,main.dz_num,main.zt_num,
isnull(ug.product_produce_no,'') as product_produce_no,
isnull(ug.product_no,'') as prod_product_no,
'' as last_active_no,
'' as cj_product_no,
'' as dj_product_no
from (select sod.product_no,sod.product_name_cn,
sum(sod.order_num) as order_num,
sum(sod.destine_num) as destine_num,
sum(sod.delivery_num) as delivery_num,
sum(sod.undelivery_num) as undelivery_num,
min(sod.delivery_date) as first_delivery_date,
max(sod.delivery_date) as last_delivery_date,
max(sod.ug_product_no) as ug_product_no,
max(sod.ug_product_name_cn) as ug_product_name_cn,
CAST(0 as float) as mr_remain_num,
CAST(0 as float) as mjg_num,
CAST(0 as float) as rcl_num,
CAST(0 as float) as cjg_num,
CAST(0 as float) as dz_num,
CAST(0 as float) as zt_num
from #tbOrderDetail sod
outer apply(select top 1 product_produce_no,product_no,bom_level
from TA04_product_part_bom
where output_product_no=sod.ug_product_no
and main_tag='T'
order by material_no)ug
where sod.ug_product_no=''
group by sod.product_no,sod.product_name_cn
)main
outer apply(select SUM(remain_num) as remain_num
from T205_stock_remain sr
where sr.product_no=main.product_no)srProduct
outer apply(select SUM(remain_num) as remain_num
from T205_stock_remain sr
where sr.product_no=main.ug_product_no)srUgProduct
outer apply(select SUM(plan_num-delivery_num) as undelivery_num
from TA03_PD_plan_detail pd
where pd.product_no=main.ug_product_no
and state_type='run')pd
outer apply(select top 1 product_no,product_produce_no,material_no,bom_level
from TA04_product_part_bom
where output_product_no=main.product_no
and product_no=main.product_no
and material_no=main.product_no
and main_tag='T'
order by material_no)ug
outer apply(select ppb.material_no,ppb.material_rate,ppb.bom_level
from TA04_product_part_bom ppb
left join T200_product_code pc on pc.product_no=ppb.material_no
where ppb.output_product_no=ug.material_no
and ppb.product_no=ug.product_no
and ppb.product_produce_no=ug.product_produce_no
and LEN(ppb.bom_level)=LEN(ug.bom_level)+4
and ppb.main_tag='T'
and pc.self_purchase='self_made')mr
Left Join T200_object_code ocMaterial on ocMaterial.object_no=mr.material_no
print '5:' + convert(varchar(40),getdate(), 121)
--更新最后一道工序
update main
set main.last_active_no=isnull(ppc.active_no,'')
from #tbResult main
outer apply(select top 1 ppc.active_no
from TA04_product_part_craft ppc
where ppc.output_product_no=main.material_no
and ppc.product_no=main.product_no
and ppc.product_produce_no=main.product_produce_no
order by craft_sequence desc)ppc
--更新车件品名
update main
set main.cj_product_no=isnull(ppb.material_no,'')
from #tbResult main
outer apply(select top 1 ppb.material_no
from TA04_product_part_bom ppb
where ppb.output_product_no=main.material_no
and ppb.product_no=main.product_no
and ppb.product_produce_no=main.product_produce_no
and ppb.main_tag='T'
order by ppb.material_no
)ppb
--更新锻件品名
update main
set main.dj_product_no=isnull(ppb.material_no,'')
from #tbResult main
outer apply(select top 1 ppb.material_no
from TA04_product_part_bom ppb
where ppb.output_product_no=main.cj_product_no
and ppb.product_no=main.product_no
and ppb.product_produce_no=main.product_produce_no
and ppb.main_tag='T'
order by ppb.material_no
)ppb
print '6:' + convert(varchar(40),getdate(), 121)
--更新材料完工品数(完工工序是最后一道工序的库存)
--完工品：内圈/外圈等材料对应的库存
--磨加工：内圈/外圈对应磨加工 的在制库存（按照品名编码找车间在制，除热处理工序）
--热处理：内圈/外圈对应热处理 工序的在制库存
--车加工：内圈/外圈对应下一级车件的车间在制库存
--锻造：内圈/外圈下一级车件对应的原材料库存
--在途：内圈/外圈下一级车件对应的原材料采购在途数
update main
set main.mr_remain_num=ISNULL(sr.remain_num,0),
main.mjg_num=ISNULL(mjg.mjg_remain_num,0),
main.rcl_num=ISNULL(mjg.rcl_remain_num,0),
main.cjg_num=ISNULL(cj.remain_num,0),
main.dz_num=ISNULL(dj.remain_num,0),
main.zt_num=ISNULL(sod.order_num,0)
from #tbResult main
outer apply(select SUM(remain_num) as remain_num
from T205_stock_remain
where product_no=main.material_no
and finish_active_no=main.last_active_no)sr
outer apply(select SUM(case when finish_active_no='RCL' then remain_num
else 0 end) as rcl_remain_num,
SUM(case when finish_active_no<>'RCL' then remain_num
else 0 end) as mjg_remain_num
from T205_stock_remain
where product_no=main.material_no
and finish_active_no<>main.last_active_no
)mjg
outer apply(select SUM(remain_num) as remain_num
from T205_stock_remain
where product_no=main.cj_product_no)cj
outer apply(select SUM(remain_num) as remain_num
from T205_stock_remain
where product_no=main.dj_product_no)dj
outer apply(select SUM(order_num-delivery_num) as order_num
from T202_supply_order_detail
where product_no=main.dj_product_no
and audit_tag='T'
and state_type='run')sod
print '7:' + convert(varchar(40),getdate(), 121)
select *
from #tbResult main
select distinct sale_order_guid,product_no
from #tbOrderDetail
print '8:' + convert(varchar(40),getdate(), 121)
End
152
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
