with 的使用

最新推荐文章于 2022-11-19 20:30:30 发布

qqqwwwqw

最新推荐文章于 2022-11-19 20:30:30 发布

阅读量359

点赞数

分类专栏： SqlServer 文章标签： object join table c

本文链接：https://blog.csdn.net/qqqwwwqw/article/details/4660291

版权

SqlServer 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

begin tran
               declare @count int
               select @count = count(1) from [@FSCXFEMPREQITM] where DocEntry = @Doc
               while(@count > 0)
                 begin
             if(object_id('tempdb..#tempFSCXFEMPREQITM') > 0)
       drop table #tempFSCXFEMPREQITM
       select * into #tempFSCXFEMPREQITM from
       (
       select b.* from [@FSCXFEMPREQITM] b
        where b.DocEntry >= @Doc and b.U_Stts = '0' and exists(
          select 1 from [@FSCXFEMPREQITM] d
          where isnull(b.U_DealCode,'') = isnull(d.U_DealCode,'') and b.U_ItemCode = d.U_ItemCode
                               and b.U_FromCode = d.U_FromCode
          and b.U_ItemAge = d.U_ItemAge
                               and isnull(b.U_LottoCode,'') = isnull(d.U_LottoCode,'') and b.U_SWhsEntry = d.U_SWhsEntry
          and b.U_PosCode = d.U_PosCode and b.U_DocType = d.U_DocType and d.DocEntry = @Doc
       )) a
       ;with Hong as (
          select DocEntry,cast(U_Num as decimal(19,6)) U_Num,cast(U_Numed as decimal(19,6)) U_Numed,cast(U_Qty as decimal(19,6)) U_Qty,cast(U_Qtyed as decimal(19,6)) U_Qtyed,parentEntry=(select min(DocEntry) from #tempFSCXFEMPREQITM where DocEntry > @Doc) from #tempFSCXFEMPREQITM where DocEntry = @Doc
          union all
          select a.DocEntry,cast(b.U_Num-b.U_Numed as decimal(19,6)),cast(a.U_Numed as decimal(19,6)) ,cast(b.U_Qty-b.U_Qtyed as decimal(19,6)),cast(a.U_Qtyed as decimal(19,6)),parentEntry = (select DocEntry from #tempFSCXFEMPREQITM b where b.DocEntry > a.DocEntry and not exists(select * from #tempFSCXFEMPREQITM c where c.DocEntry > a.DocEntry and b.DocEntry > c.DocEntry) )
          from #tempFSCXFEMPREQITM a inner join Hong b on a.DocEntry = b.parentEntry
       )
       update a set a.U_Num = b.U_Num,a.U_Qty = b.U_Qty from [@FSCXFEMPREQITM] a inner join Hong b on a.DocEntry = b.DocEntry
                            and b.U_Num-b.U_Numed > 0 and b.U_Qty - b.U_Qtyed > 0
                            set @count = @count-1
                    end
                  if(@@error <> 0)
                      rollback tran
                  commit tran
                  if(object_id('tempdb..#tempFSCXFEMPREQITM') > 0)
       drop table #tempFSCXFEMPREQITM