一种方法是关联子查询:

One method is a correlated subquery:

select t1.*,
       (select t2.u_price
        from t2
        where t2.item = t1.item and t2.fromdate <= t1.transactiondate
        order by t2.fromdate desc
        fetch first 1 row only
       ) as u_price
from t1;

并非所有数据库都支持标准的 fetch 子句.您的数据库可能使用 limit、select top 或其他内容.

Not all databases support the standard fetch clause. Your database might use limit, select top or something different.

在 MS Access 中，您将使用:

In MS Access, you would use:

选择 t1.*,(选择前 1 名 t2.u_price从 t2其中 t2.item = t1.item 和 t2.fromdate

select t1.*, (select top 1 t2.u_price from t2 where t2.item = t1.item and t2.fromdate <= t1.transactiondate order by t2.fromdate desc ) as u_price from t1;