sql - 两个 select 语句的求和结果

Question

我们的员工每周都会获得佣金，由于奖金结构，我必须计算单独两周的工资，然后将两者相加。

我有以下 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)

这为我提供了所需的数据，但我想将两个结果合并到一个具有相同列的表中，但将一些列添加在一起(CommissionPay、PTOPay、HolidayPay、Overtime、TotalPay)。做这个的最好方式是什么？我使用的是 SQL Server 2008 R2。

Answer 1

试试这个

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