如何用Join简化嵌套子查询SQL语句，提升查询效率？

尝试简化以下嵌套子查询的SQL语句，当前嵌套方式运行耗时极久，希望改用Join实现但无法得到准确结果。

原查询语句 #

SELECT 
    T1.company,
    T1.project, 
    T1.ID, 
    T1.date,
    T1.qty, 
    (
        SELECT MAX(T2.cost)
        FROM Hour_Cost  AS T2
        WHERE T2.company = T1.company AND
        T2.id = T1.ID AND
        T2.trans_date =
        (
            SELECT MAX(T3.date)
            FROM Hour_Cost AS T3
            WHERE T3.company = T2.company AND
            T3.id = T2.id AND
            T3.trans_date <= T1.date
        )
    ) AS PRICE
FROM Empl_Table AS T1

样本数据 #

Empl_Table #

id  date
1   5/31/2015
1   11/15/2015
1   11/15/2015
1   11/15/2015
1   12/22/2015
1   12/24/2015
1   12/25/2015
1   1/1/2016
1   1/5/2016
1   1/11/2016
1   1/18/2016
1   1/25/2016
1   4/15/2016
1   4/27/2016
2   10/5/2018
2   10/8/2018
2   10/9/2018
2   10/10/2018
2   10/11/2018
2   10/12/2018
2   2/1/2019
2   2/4/2019
2   2/5/2019
2   2/6/2019
2   2/7/2019
2   2/8/2019
2   2/11/2019
2   2/12/2019
2   2/13/2019
2   2/14/2019
2   2/15/2019

Hour_Cost #

date    id  cost
12/1/2015   1   79.53
1/1/2016    1   64.49
1/1/2018    2   69.59
1/1/2019    2   62.45
1/1/2020    2   60.37
1/1/2021    2   63.79

期望结果 #

ID  date        OUTPUT
1   5/31/2015   0.00
1   11/15/2015  0.00
1   11/15/2015  0.00
1   11/15/2015  0.00
1   12/22/2015  79.53
1   12/24/2015  79.53
1   12/25/2015  79.53
1   1/1/2016    64.49
1   1/5/2016    64.49
1   1/11/2016   64.49
1   1/18/2016   64.49
1   1/25/2016   64.49
1   4/15/2016   64.49
1   4/27/2016   64.49
2   10/5/2018   69.59
2   10/8/2018   69.59
2   10/9/2018   69.59
2   10/10/2018  69.59
2   10/11/2018  69.59
2   10/12/2018  69.59
2   2/1/2019    62.45
2   2/4/2019    62.45
2   2/5/2019    62.45
2   2/6/2019    62.45
2   2/7/2019    62.45
2   2/8/2019    62.45
2   2/11/2019   62.45
2   2/12/2019   62.45
2   2/13/2019   62.45
2   2/14/2019   62.45
2   2/15/2019   62.45

解决方案 #

方案1：LEFT JOIN + 子查询匹配最新日期 #

该方案直接将原嵌套逻辑转化为Join形式，保留结果准确性：

SELECT 
    T1.ID,
    T1.date,
    COALESCE(hc.cost, 0.00) AS OUTPUT
    -- 如需保留原查询的company、project、qty字段，直接添加到SELECT列表即可
FROM Empl_Table AS T1
LEFT JOIN Hour_Cost AS hc 
    ON hc.id = T1.ID 
    AND hc.date = (
        SELECT MAX(date) 
        FROM Hour_Cost 
        WHERE id = T1.ID AND date <= T1.date
    )

方案2：使用LATERAL JOIN/OUTER APPLY（适用于PostgreSQL、SQL Server等） #

该方式性能更优，尤其适合大数据量场景：

-- SQL Server版本
SELECT 
    T1.ID,
    T1.date,
    COALESCE(h.cost, 0.00) AS OUTPUT
FROM Empl_Table AS T1
OUTER APPLY (
    SELECT TOP 1 cost
    FROM Hour_Cost
    WHERE id = T1.ID AND date <= T1.date
    ORDER BY date DESC
) AS h

-- PostgreSQL版本
SELECT 
    T1.ID,
    T1.date,
    COALESCE(h.cost, 0.00) AS OUTPUT
FROM Empl_Table AS T1
LEFT JOIN LATERAL (
    SELECT cost
    FROM Hour_Cost
    WHERE id = T1.ID AND date <= T1.date
    ORDER BY date DESC
    LIMIT 1
) AS h ON true

性能优化建议 #

为进一步提升查询速度，建议在Hour_Cost表创建复合索引：

CREATE INDEX idx_hourcost_id_date ON Hour_Cost(id, date);

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