批次累加到指定数量sqlserver

已于 2022-10-18 10:54:02 修改 · 287 阅读

文章标签：

#开发语言 #数据结构

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

sqlserver 专栏收录该内容

1 篇文章

订阅专栏

这段内容展示了如何使用SQL创建数据库表`TestTable`并插入数据，然后通过窗口函数进行库存计算，特别是针对'A'系列产品的操作。查询涉及到对每个批次的用量计算，确保不超过35的总和。

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