批次累加到指定数量sqlserver

豆豆和小尾巴

已于 2022-10-18 10:54:02 修改

阅读量228

点赞数 1

分类专栏： sqlserver 文章标签：开发语言数据结构

于 2022-10-18 10:43:10 首次发布

本文链接：https://blog.csdn.net/weixin_47773575/article/details/127383619

版权

sqlserver 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

CREATE TABLE [dbo].[TestTable](
   [batch] [nchar](10) NULL,
   [num] [decimal](18, 0) NULL,
   [catalogNo] [nchar](10) NULL
) ON [PRIMARY]
GO

--insert into TestTable ([batch],[num],[catalogNo]) values ('A1',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('A2',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('A3',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('A4',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('A5',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('A6',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('A7',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('A8',10,'A')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B1',10,'B')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B2',10,'B')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B3',10,'B')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B4',10,'B')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B5',10,'B')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B6',10,'B')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B7',10,'B')
--insert into TestTable ([batch],[num],[catalogNo]) values ('B8',10,'B')

select 产品号=catalogNo,批次号=batch,批次库存=total_amount,每个批次的用量=iif(xx<0,total_amount,total_amount-xx) from
(
select catalogNo,batch,total_amount,
   sum(total_amount) over(partition by catalogNo order by batch rows between unbounded preceding and current row) as amount,
   xx=sum(total_amount) over(partition by catalogNo order by batch rows between unbounded preceding and current row)-35
   from
   (select catalogNo,batch,sum(num) as total_amount
   from TestTable
   group by catalogNo,batch) as a
   )x where catalogNo='A' and xx<=amount and xx<=total_amount