如何优化简化含多个关联子查询的T-SQL查询语句

原查询从where DateLoad开始的逻辑重复对同一张表做了4次关联子查询匹配员工维度的最小值，写法冗余且数据量大时性能很差，可以通过一次性计算分组最小值的方式改写，逻辑和原查询完全一致，同时大幅简化代码、提升执行效率。

推荐改写方案（窗口函数版，支持SQL Server 2005及以上版本） #

窗口函数可以在单次表扫描中计算出每个Employee_Number分组下需要匹配的所有最小值，不需要重复写多个关联子查询：

WITH EmpBase AS (
    SELECT 
        *,
        MIN(DateLoad) OVER(PARTITION BY Employee_Number) AS group_min_DateLoad,
        MIN(Employee_Type) OVER(PARTITION BY Employee_Number) AS group_min_EmployeeType,
        MIN(Status_Type) OVER(PARTITION BY Employee_Number) AS group_min_StatusType,
        MIN(Hire_Date) OVER(PARTITION BY Employee_Number) AS group_min_HireDate
    FROM dbo.TableMain
)
SELECT 
    Employee_Number, 
    DateLoad, 
    MIN(Rate_1_Pay_Rate) AS Rate_1_Pay_Rate,
    MIN(Hourly_Rate) AS Hourly_Rate, 
    MIN(FLSA_Status) AS FLSA_Status,  
    MIN(Hire_Date) AS Hire_Date, 
    MIN(Employee_Type) AS Employee_Type,
    MIN(Status_Type) AS Status_Type
FROM EmpBase
WHERE 
    DateLoad = group_min_DateLoad
    AND Employee_Type = group_min_EmployeeType
    AND Status_Type = group_min_StatusType
    AND Hire_Date = group_min_HireDate
GROUP BY Employee_Number, DateLoad

兼容老版本的改写方案 #

如果使用的是SQL Server 2005以前不支持窗口函数的版本，可以先聚合出每个员工的所有最小值，再关联主表筛选，性能同样远优于原写法：

SELECT 
    hist.Employee_Number, 
    hist.DateLoad, 
    MIN(hist.Rate_1_Pay_Rate) AS Rate_1_Pay_Rate,
    MIN(hist.Hourly_Rate) AS Hourly_Rate, 
    MIN(hist.FLSA_Status) AS FLSA_Status,  
    MIN(hist.Hire_Date) AS Hire_Date, 
    MIN(hist.Employee_Type) AS Employee_Type,
    MIN(hist.Status_Type) AS Status_Type
FROM dbo.TableMain AS hist
JOIN (
    SELECT 
        Employee_Number,
        MIN(DateLoad) AS min_DateLoad,
        MIN(Employee_Type) AS min_EmployeeType,
        MIN(Status_Type) AS min_StatusType,
        MIN(Hire_Date) AS min_HireDate
    FROM dbo.TableMain
    GROUP BY Employee_Number
) AS emp_group_min
    ON hist.Employee_Number = emp_group_min.Employee_Number
    AND hist.DateLoad = emp_group_min.min_DateLoad
    AND hist.Employee_Type = emp_group_min.min_EmployeeType
    AND hist.Status_Type = emp_group_min.min_StatusType
    AND hist.Hire_Date = emp_group_min.min_HireDate
GROUP BY hist.Employee_Number, hist.DateLoad

改写收益 #

• 消除了原写法中4个逐行触发的相关子查询，避免同表重复扫描，数据量越大性能提升越明显
• 筛选条件逻辑集中，没有重复的子查询结构，后续调整字段匹配规则时只需要修改一处即可，可维护性更强
• 完全保留原查询的业务逻辑，返回结果和原写法100%一致

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