如何用T-SQL高效匹配工时表与人工费率计算人工成本

我需要解决的问题是：根据工时表中的Date（工作日期）、EmpID（员工ID）、Rate（费率类型），匹配人工费率表中对应该员工、费率类型，且生效日期（RateStart）不晚于工作日期的最新费率金额（RateAmount），再通过HoursWorked * RateAmount计算出人工成本（LabourCost）。之前用PowerQuery/M实现但查询速度极慢，需要高效的T-SQL方案。

工时表（Timesheet） #

人工费率表（EmployeeRates） #

高效T-SQL实现方案 #

第一步：创建索引（提升查询性能） #

要确保查询高效，先给两张表创建复合索引，避免全表扫描：

-- 给工时表创建复合索引，覆盖查询所需字段
CREATE NONCLUSTERED INDEX IX_Timesheet_EmpID_Rate_Date
ON Timesheet (EmpID, Rate, Date)
INCLUDE (HoursWorked);

-- 给费率表创建复合索引，快速定位有效费率
CREATE NONCLUSTERED INDEX IX_EmployeeRates_EmpID_Rate_RateStart
ON EmployeeRates (EmpID, Rate, RateStart)
INCLUDE (RateAmount);

第二步：查询语句（匹配费率并计算人工成本） #

使用CROSS APPLY结合TOP 1，为每条工时记录精准匹配最新有效费率：

SELECT
    t.Date,
    t.EmpID,
    t.Rate,
    t.HoursWorked,
    er.RateAmount,
    t.HoursWorked * er.RateAmount AS LabourCost
FROM
    Timesheet t
CROSS APPLY (
    SELECT TOP 1 RateAmount
    FROM EmployeeRates er
    WHERE
        er.EmpID = t.EmpID
        AND er.Rate = t.Rate
        AND er.RateStart <= t.Date
    ORDER BY er.RateStart DESC
) er
ORDER BY
    t.EmpID,
    t.Date,
    t.Rate;

批量更新工时表字段 #

如果需要直接更新工时表中的RateAmount和LabourCost字段，可使用以下语句：

UPDATE t
SET
    t.RateAmount = er.RateAmount,
    t.LabourCost = t.HoursWorked * er.RateAmount
FROM
    Timesheet t
CROSS APPLY (
    SELECT TOP 1 RateAmount
    FROM EmployeeRates er
    WHERE
        er.EmpID = t.EmpID
        AND er.Rate = t.Rate
        AND er.RateStart <= t.Date
    ORDER BY er.RateStart DESC
) er;

内容的提问来源于stack exchange，提问作者Murdo900