用求和案求和(Summing using sum case)

用求和案求和(Summing using sum case)

我有两个表格：

MonthlySalary]: MS_EmployeeID MS_Semel MS_Month MS_Amount 22222 9 1 4000 22222 9 2 4000 22222 9 3 4000 22222 9 4 5000 22222 9 5 5000 22222 9 6 3000 22222 9 7 2000 22222 9 8 5000 MSVTransaction: MSV_EntitledIdNumber MSV_PaymentAmount MSV_Month 22222 3000 1 22222 3000 2 22222 4000 3 22222 5000 4 22222 6000 6 22222 7000 7 22222 2000 8 22222 5000 9

我正在尝试建立一个比较表。 输出应该是这样的：

MSV_EntitledIdNumber MS_Semel Jan_MSV Jan_SML .... Aug_MSV Aug_SML 22222 9 3000 4000 5000 5000

我开始写这个：

select distinct [MSV_EntitledIdNumber], [MS_Semel], sum(case when [MSV_Month] in (1) then [MSV_PaymentAmount] else 0 end) as JanMSV, sum(case when [MS_Month] in (1) then [MS_Amount] else 0 end) as JanSML, sum(case when [MSV_Month] in (2) then [MSV_PaymentAmount] else 0 end) as FebMSV, sum(case when [MS_Month] in (2) then [MS_Amount] else 0 end) as FebSML, sum(case when [MSV_Month] in (3) then [MSV_PaymentAmount] else 0 end) as MarMSV, sum(case when [MS_Month] in (3) then [MS_Amount] else 0 end) as MarSML, sum(case when [MSV_Month] in (4) then [MSV_PaymentAmount] else 0 end) as AprMSV, sum(case when [MS_Month] in (4) then [MS_Amount] else 0 end) as AprSML, sum(case when [MSV_Month] in (5) then [MSV_PaymentAmount] else 0 end) as MayMSV, sum(case when [MS_Month] in (5) then [MS_Amount] else 0 end) as MaySML, sum(case when [MSV_Month] in (6) then [MSV_PaymentAmount] else 0 end) as JunMSV, sum(case when [MS_Month] in (6) then [MS_Amount] else 0 end) as JunSML, sum(case when [MSV_Month] in (7) then [MSV_PaymentAmount] else 0 end) as JulMSV, sum(case when [MS_Month] in (7) then [MS_Amount] else 0 end) as JulSML, sum(case when [MSV_Month] in (8) then [MSV_PaymentAmount] else 0 end) as AugMSV, sum(case when [MS_Month] in (8) then [MS_Amount] else 0 end) as AugSML, sum(case when [MSV_Month] between 1 and 8 then [MSV_PaymentAmount] else 0 end) as TotalMSV, sum(case when [MS_Month] between 1 and 8 then [MS_Amount] else 0 end) as TotalSML, sum(case when [MSV_Month] between 1 and 8 then [MSV_PaymentAmount] else 0 end) - sum(case when [MS_Month] between 1 and 8 then [MS_Amount] else 0 end) as Delta from [dbo].[MSVTransaction] as msv inner join [dbo].[MonthlySalary] as SmlTbl on SmlTbl.MS_EmployeeID = msv.MSV_EntitledIdNumber [MS_Semel] = '9' and msv.[MSV_EntitledIdNumber] in (select distinct [MS_EmployeeID] from [dbo].[MonthlySalary]) group by [MSV_EntitledIdNumber],[MS_Semel]

我得到的结果不正确。 知道代码有什么问题，如何修复它以获得我想要的输出？ 谢谢！

I have two tabels:

MonthlySalary]: MS_EmployeeID MS_Semel MS_Month MS_Amount 22222 9 1 4000 22222 9 2 4000 22222 9 3 4000 22222 9 4 5000 22222 9 5 5000 22222 9 6 3000 22222 9 7 2000 22222 9 8 5000 MSVTransaction: MSV_EntitledIdNumber MSV_PaymentAmount MSV_Month 22222 3000 1 22222 3000 2 22222 4000 3 22222 5000 4 22222 6000 6 22222 7000 7 22222 2000 8 22222 5000 9

And I'm trying to build a comparison table. The output should be like this:

MSV_EntitledIdNumber MS_Semel Jan_MSV Jan_SML .... Aug_MSV Aug_SML 22222 9 3000 4000 5000 5000

I started writing this:

select distinct [MSV_EntitledIdNumber], [MS_Semel], sum(case when [MSV_Month] in (1) then [MSV_PaymentAmount] else 0 end) as JanMSV, sum(case when [MS_Month] in (1) then [MS_Amount] else 0 end) as JanSML, sum(case when [MSV_Month] in (2) then [MSV_PaymentAmount] else 0 end) as FebMSV, sum(case when [MS_Month] in (2) then [MS_Amount] else 0 end) as FebSML, sum(case when [MSV_Month] in (3) then [MSV_PaymentAmount] else 0 end) as MarMSV, sum(case when [MS_Month] in (3) then [MS_Amount] else 0 end) as MarSML, sum(case when [MSV_Month] in (4) then [MSV_PaymentAmount] else 0 end) as AprMSV, sum(case when [MS_Month] in (4) then [MS_Amount] else 0 end) as AprSML, sum(case when [MSV_Month] in (5) then [MSV_PaymentAmount] else 0 end) as MayMSV, sum(case when [MS_Month] in (5) then [MS_Amount] else 0 end) as MaySML, sum(case when [MSV_Month] in (6) then [MSV_PaymentAmount] else 0 end) as JunMSV, sum(case when [MS_Month] in (6) then [MS_Amount] else 0 end) as JunSML, sum(case when [MSV_Month] in (7) then [MSV_PaymentAmount] else 0 end) as JulMSV, sum(case when [MS_Month] in (7) then [MS_Amount] else 0 end) as JulSML, sum(case when [MSV_Month] in (8) then [MSV_PaymentAmount] else 0 end) as AugMSV, sum(case when [MS_Month] in (8) then [MS_Amount] else 0 end) as AugSML, sum(case when [MSV_Month] between 1 and 8 then [MSV_PaymentAmount] else 0 end) as TotalMSV, sum(case when [MS_Month] between 1 and 8 then [MS_Amount] else 0 end) as TotalSML, sum(case when [MSV_Month] between 1 and 8 then [MSV_PaymentAmount] else 0 end) - sum(case when [MS_Month] between 1 and 8 then [MS_Amount] else 0 end) as Delta from [dbo].[MSVTransaction] as msv inner join [dbo].[MonthlySalary] as SmlTbl on SmlTbl.MS_EmployeeID = msv.MSV_EntitledIdNumber [MS_Semel] = '9' and msv.[MSV_EntitledIdNumber] in (select distinct [MS_EmployeeID] from [dbo].[MonthlySalary]) group by [MSV_EntitledIdNumber],[MS_Semel]

The results I'm getting are not correct. Any idea what is the problem with the code and how can I fix it in order to get the output I want? Thank you!

最满意答案

我认为问题是from子句。 尝试加入月份编号并使用外部联接：

select . . . from [dbo].[MSVTransaction] msv left join [dbo].[MonthlySalary] SmlTbl on SmlTbl.MS_EmployeeID = msv.MSV_EntitledIdNumber and smltbl.ms_month = msv.msv_month [MS_Semel] = '9' group by msv.[MSV_EntitledIdNumber], [MS_Semel]

I think the problem is the from clause. Try joining on the month number and using an outer join:

select . . . from [dbo].[MSVTransaction] msv left join [dbo].[MonthlySalary] SmlTbl on SmlTbl.MS_EmployeeID = msv.MSV_EntitledIdNumber and smltbl.ms_month = msv.msv_month [MS_Semel] = '9' group by msv.[MSV_EntitledIdNumber], [MS_Semel]