在单个访问查询中直接和加班求和

Question

我有一个包含作业考勤卡记录的访问表：

工作 ID 小时工作费率101    1.25101 0.75 OT102 0.33 DT101    0.50103    2.00

我想要一个查询，该查询为对HoursWorked字段求和的每个JobID返回一条记录。 如果[Rate] = "OT"则HoursWorked应乘以 1.5，或者在 DT 的情况下乘以 2。

结果如下所示：

作业 ID 总工作小时数101    2.875102    0.66103    2.00

我想出了这个查询，它成功地对不同类型的Rate求和：

SELECT JobID, Sum(HoursWorked) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID, Rate;

但是我被困在如何将不同的费率乘以并返回 JobID 的单个总数上。

Answer 1

使用 Switch 表达式计算每个 Rate 的乘数。 将工作时间乘以这些值，并将其全部Sum()为GROUP BY

SELECT
    t.JobID,
    Sum(t.HoursWorked * Switch(t.Rate='OT',1.5,t.Rate='DT',2,True,1)) AS TotalHoursWorked
FROM Timecards AS t
GROUP BY t.JobID;

在 Access 2010 中使用您的数据进行测试时，该查询返回了预期结果。

这是Switch表达式分解和格式化。 它返回表达式计算结果为 True 的第一个表达式/值对的值，并忽略任何剩余的对。 最终的表达式/值对 （ True, 1 ） 对于任何与 'OT' 或 'DT' 不匹配的速率返回 1：

Switch(
        t.Rate='OT', 1.5,
        t.Rate='DT', 2,
        True, 1
    )

Answer 2

可以使用 IIF 函数有条件地确定要聚合的内容：

SELECT 
  JobID, 
  SUM(IIF(rate = 'OT', HoursWorked * 1.5, IIF(rate = 'DT' ,HoursWorked * 2.0, HoursWorked))) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID;

逻辑是 iif（布尔测试，值如果真，值如果假）

由于您有两个条件要测试，因此您必须嵌套 iif 表达式，因此如果 rate = 'OT' 然后乘以 1,5，否则检查 rate = 'DT' 如果是，则乘以 2，或者按原样使用值，如果 rate 是其他东西。

此外，使用聚合函数时，不应按聚合的列进行分组。如果你这样做，你很可能得不到你想要的结果。

Answer 3

试试这个：

SELECT 
    JobID, 
    Sum(HoursWorked * CASE 
                        WHEN rate = 'OT' THEN 1.5 
                        WHEN rate = 'DT' THEN 2
                        ELSE 1
                    END
        ) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID