将相关子查询转换为连接

I want to express this:

我想表达这个:

SELECT
    a.*
    ,b.timestamp_col
FROM weird_data_source a
LEFT JOIN weird_data_source b
ON a.id = b.id
    AND b.timestamp_col = (
        SELECT
            MAX(sub.timestamp_col)
        FROM weird_data_source sub
        WHERE sub.id = a.id
            AND sub.date_col <= a.date_col
            AND sub.timestamp_col < a.timestamp_col
    )
SELECT