用求和案求和(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 9And 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 5000I 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]更多推荐
发布评论