如何在SQL中获取设置标记后每组的总和

Question

我有一个表，用来存储散装化学品储存设施的库存票。我对它进行了设置，这样用户就可以为每一批运出或运入的化学品输入一张票证。用户还每月(或者有时更频繁地)进行实物盘点，并且记录被存储，就像常规票据一样，只检查实物盘点位列。该表包含ID (标识)、票号、储油罐、已移动的加仑数和实际库存的列。我需要运行一个查询，该查询将对上次库存票据(包括每个油箱的库存票据)之后的每个油箱的总加仑数进行求和。我现在的查询是在为每个坦克创建的最后一个库存票据之后对所有内容进行求和。

    select t.Tank as Tank, p.ProductName as product, t.Capacity as Capacity, sum(l.Gallons) as Total, t.Capacity - sum(l.Gallons) as EmptySpace
from AGP_BlkInv_Log as l join AGP_BlkInv_Tanks as t on l.TankID = t.TankID join AGP_BlkInv_Products as p on t.ProductID = p.ProductID
where l.ID >= (select max(ID) from dbo.AGP_BlkInv_Log where PhyisicalInv = 1 group by TankID, ID) and t.Void = 0 and t.Tank not like 'f%'
group by t.Tank, p.ProductName, t.Capacity

我看过交叉应用和分区，它们可能是我需要的解决方案，但我不知道如何使用它们来获得我想要的东西。

此外，这是一个新的项目，所以数据库结构可以改变，如果它会让事情变得更容易。

我正在运行MS-SQL server 2012

编辑:不确定这是否是您要查找的内容，但以下是用于创建表的脚本

`CREATE TABLE [dbo].[AGP_BlkInv_Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nchar](50) NOT NULL,
[Void] [bit] NOT NULL,
CONSTRAINT [PK_AGP_BlkInv_Products] PRIMARY KEY CLUSTERED 
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AGP_BlkInv_Products] ADD  CONSTRAINT 
[DF_AGP_BlkInv_Products_Void]  DEFAULT ((0)) FOR [Void]
GO`

CREATE TABLE [dbo].[AGP_BlkInv_Tanks](
[TankID] [int] IDENTITY(1,1) NOT NULL,
[Tank] [nchar](25) NOT NULL,
[Capacity] [float] NOT NULL,
[ProductID] [int] NOT NULL,
[Void] [bit] NOT NULL,
CONSTRAINT [PK_AGP_BlkInv_Tanks] PRIMARY KEY CLUSTERED 
(
[TankID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AGP_BlkInv_Tanks] ADD  CONSTRAINT
[DF_AGP_BlkInv_Tanks_Void] 
DEFAULT ((0)) FOR [Void]
GO

ALTER TABLE [dbo].[AGP_BlkInv_Tanks]  WITH CHECK ADD  CONSTRAINT
[FK_AGP_BlkInv_Tanks_AGP_BlkInv_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[AGP_BlkInv_Products] ([ProductID])
GO

ALTER TABLE [dbo].[AGP_BlkInv_Tanks] CHECK CONSTRAINT 
[FK_AGP_BlkInv_Tanks_AGP_BlkInv_Products]
GO

CREATE TABLE [dbo].[AGP_BlkInv_Log](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TankID] [int] NOT NULL,
[Ticket] [int] NOT NULL,
[Gallons] [float] NOT NULL,
[PhyisicalInv] [bit] NOT NULL,
[Void] [bit] NOT NULL,
CONSTRAINT [PK_AGP_BlkInv_Log] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AGP_BlkInv_Log] ADD  CONSTRAINT  
[DF_AGP_BlkInv_Log_PhyisicalInv]  DEFAULT ((0)) FOR [PhyisicalInv]
GO

ALTER TABLE [dbo].[AGP_BlkInv_Log] ADD  CONSTRAINT [DF_AGP_BlkInv_Log_Void]  
DEFAULT ((0)) FOR [Void]
GO

ALTER TABLE [dbo].[AGP_BlkInv_Log]  WITH CHECK ADD  CONSTRAINT           
[FK_AGP_BlkInv_Log_AGP_BlkInv_Tanks] FOREIGN KEY([TankID])
REFERENCES [dbo].[AGP_BlkInv_Tanks] ([TankID])
GO

ALTER TABLE [dbo].[AGP_BlkInv_Log] CHECK CONSTRAINT                    
[FK_AGP_BlkInv_Log_AGP_BlkInv_Tanks]
GO

通过ProductID将产品表链接到储罐，通过TankID将储罐表链接到日志

嗯..。我不能发布图片，所以我会尽我最大的努力输入一些样本数据

Product Table
ProductID,  ProductName,  Void
1           chem1         0
2           chem2         0
3           chem3         0

Tank Table
TankID,     TankName,     Capacity,    ProductID,    Void
1           East50        50000        1             0
2           West50        50000        2             0
3           North30       30000        3             0

Log Table
ID,   TankID,  Ticket,  Gallons,  PhysicalInv, Void
1     1        1234     500       0            0
2     2        1235     300       0            0
3     3        1236     150       0            0
4     1        9999     4000      1            0
5     2        9999     4000      1            0
6     3        9999     3000      1            0
7     1        1239     -500      0            0

我想要得到的结果是

Tank,  Product, Capacity, Gallons, EmptySpace
East50 chem1    50000     3500     46500
West50 chem2    50000     4000     46000
North30 chem3   30000     3000     27000

查询应忽略最后一个库存条目之前的所有条目，并对每个油箱的所有条目求和，包括最后一组库存条目。

Answer 1

您的子查询

(select max(ID) from dbo.AGP_BlkInv_Log where PhyisicalInv = 1 group by TankID, ID)

是个问题。您按ID进行分组，这基本上否定了获取max(ID)的全部意义。尝试仅按TankID分组。

(还有，PhyisicalInv？这是打字错误吗？)

希望这能有所帮助。

Answer 2

好的，在几乎扯掉我所有的头发之后，我找到了答案。交叉应用是我所需要的。我会发布我使用的代码，因为我认为，如果我试图解释它，我会让它变得如此混乱，以至于我甚至不会理解我做了什么。

    select t.Tank as Tank, p.ProductName as product, t.Capacity as Capacity, sum(l.Gallons) as Total, t.Capacity - sum(l.Gallons) as EmptySpace
from AGP_BlkInv_Log as l join AGP_BlkInv_Tanks as t on l.TankID = t.TankID join AGP_BlkInv_Products as p on t.ProductID = p.ProductID
cross apply (select max(ID) as ID, TankID from dbo.AGP_BlkInv_Log where PhyisicalInv = 1 group by TankID) as c
where l.ID >= c.ID and l.TankID = c.TankID
group by t.Tank, p.ProductName, t.Capacity

这给了我需要的最终数字。