【SQL】outer apply的用法

【SQL】outer apply的用法本专栏为【数据库】,主要介绍SQL的功能与特点、SQL数据定义语言(表、视图、索引、约束)、SQL数据操作语言(数据检索、数据插入、数据删除、数据更新)、创建与删除触发器、SQL数据控制语言(安全性和授权、事务处理)以及嵌入式SQL。如果你对本专辑感兴趣,持续关注吧。大家有任何问题,也可以评论区反馈,私信我。outerapply添加外部行outerapply返回改行,并且改行的右表表达式的属性为null。

 系列文章

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。
如果你对本专辑感兴趣,持续关注吧。大家有任何问题,也可以评论区反馈,私信我。
4f660826b73a47428b41dfa19bf0e28f.png

 

一、技术介绍

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 

 

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/34607.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注