tsql - T-SQL-每个项目的运行总计

Question

我有这个简化的表结构:



我需要生成一个汇总结果，将每个项目/期间的交易总和-每个项目的运行总额。我添加了TransactionCountInPeriod列

如何编写查询以获取如下结果:



这是我查询得到的结果...我无法弄清楚如何根据ProjectId 来运行总。

SELECT 
 prj.Id AS ProjectId,
 p.Id AS PeriodId,
 SUM(t.Amount) AS SumInPeriod--,
 --SUM(t.Amount) OVER (PARTITION BY prj.Id) AS RunningTotal
 --COUNT(t.*) AS TransactionCountInPeriod
FROM 
 Periods p
 CROSS JOIN Projects prj
 LEFT OUTER JOIN Transactions t ON p.Id = t.PeriodId
GROUP BY 
 prj.Id,
 p.Id
ORDER BY
 prj.Id,
 p.Id

架构和测试数据

CREATE TABLE [dbo].[Periods](
    [Id] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[Projects](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL
)
GO

CREATE TABLE [dbo].[Transactions](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProjectId] [int] NOT NULL,
    [PeriodId] [int] NOT NULL,
    [Amount] [decimal](18, 0) NOT NULL
)

GO
INSERT [dbo].[Periods] ([Id]) VALUES (201801)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201802)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201803)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201804)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201805)
GO
SET IDENTITY_INSERT [dbo].[Projects] ON 
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (1, N'Alpha')
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (2, N'Beta')
GO
SET IDENTITY_INSERT [dbo].[Projects] OFF
GO
SET IDENTITY_INSERT [dbo].[Transactions] ON 
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[Transactions] OFF
GO

(我会创建一个SQL提琴，但是当我尝试时，它总是崩溃……)

Answer 1

您可以使用SUM窗口函数来计算运行总计。

DECLARE @Periods TABLE(
    [Id] [int] NOT NULL
)

DECLARE @Projects TABLE(
    [Id] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
)

DECLARE @Transactions TABLE(
    [Id] [int] NOT NULL,
    [ProjectId] [int] NOT NULL,
    [PeriodId] [int] NOT NULL,
    [Amount] [decimal](18, 0) NOT NULL
)

INSERT @Periods ([Id]) VALUES (201801)
INSERT @Periods ([Id]) VALUES (201802)
INSERT @Periods ([Id]) VALUES (201803)
INSERT @Periods ([Id]) VALUES (201804)
INSERT @Periods ([Id]) VALUES (201805)
INSERT @Projects ([Id], [Name]) VALUES (1, N'Alpha')
INSERT @Projects ([Id], [Name]) VALUES (2, N'Beta')
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))

select
    pr.Id as ProjectId
    , pe.Id as PeriodId
    , isnull(tr.SumAmount, 0) as SumInPeriod
    , isnull(sum(tr.SumAmount) over(partition by pr.Id order by pe.Id), 0) as RunningTotal
    , tr.TransactionsCount as TransactionCountInPeriod
from @Projects pr
cross join @Periods pe
outer apply (select sum(t.Amount) as SumAmount, count(*) as TransactionsCount from @Transactions t where t.ProjectId = pr.Id and t.PeriodId = pe.Id)  tr
order by ProjectId, PeriodId