SQL 两个 select 语句的求和结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8647725/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum results from two select statements
提问by BrianKE
Our employees are paid commission on a weekly basis and because of a bonus structure i have to calculate two separate weeks of pay and then add the two together.
我们的员工每周都会收到佣金，由于奖金结构，我必须计算两个单独的周薪，然后将两者相加。
I have the following SQL statement which gets the two separate weeks results
我有以下 SQL 语句，它获取两个单独的星期结果
SELECT PerceptionistID, SSNLastFour, CommissionPay,
PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)
UNION
-- Need to get the following week's data and sum the two together
SELECT PerceptionistID, SSNLastFour, CommissionPay,
PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
This gets me the data I need but I would like to combine the two results into one table with the same columns but having some of the columns added together (CommissionPay, PTOPay, HolidayPay, Overtime, TotalPay). What is the best way to do this? I am using SQL Server 2008 R2.
这为我提供了我需要的数据，但我想将两个结果合并到一个具有相同列的表中，但将一些列添加在一起（CommissionPay、PTOPay、HolidayPay、Overtime、TotalPay）。做这个的最好方式是什么？我正在使用 SQL Server 2008 R2。
回答by Oleg Dok
Try this
尝试这个
SELECT PerceptionistID, SSNLastFour, SUM(CommissionPay) CommissionPay,
SUM(PTOPay) PTOPay, SUM(HolidayPay) HolidayPay, SUM(Overtime) Overtime, SUM(TotalPay) TotalPay
FROM
(
SELECT PerceptionistID, SSNLastFour, CommissionPay,
PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)
UNION ALL
-- Need to get the following week's data and sum the two together
SELECT PerceptionistID, SSNLastFour, CommissionPay,
PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
) t
GROUP BY PerceptionistID, SSNLastFour
回答by philofinfinitejest
Make your query a subquery, and group at the 'superquery' level:
使您的查询成为子查询，并在“超级查询”级别进行分组：
SELECT PerceptionistID, SSNLastFour, sum(CommissionPay), ...
FROM
(
SELECT PerceptionistID, SSNLastFour, CommissionPay,
PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)
UNION
-- Need to get the following week's data and sum the two together
SELECT PerceptionistID, SSNLastFour, CommissionPay,
PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
) AS X
GROUP BY PerceptionistID, SSNLastFour
回答by MoMo
How about modifying [dbo].fnCalculateCommissionForWeekOf or creating a new function that takes a start and end date. This way you can use the same function, and same logic, to run against any span of dates, including the past week, 2 weeks, month, year etc.
如何修改 [dbo].fnCalculateCommissionForWeekOf 或创建一个接受开始和结束日期的新函数。通过这种方式，您可以使用相同的函数和相同的逻辑来针对任何日期跨度运行，包括过去一周、两周、一个月、一年等。
