LEFT JOIN 具有特定条件
在 VBA-Ado 中(访问文件)
In VBA-Ado (Access file)
T1
id Item TransactionDate
-----------------------------
1 TV 31/12/2017
T2
id Item U_Price FromDate
--------------------------
1 TV 12.1$ 31/12/2018
2 TV 15.3$ 30/11/2019
3 TV 11.2$ 25/02/2020
4 TV 21.1$ 01/05/2016
我需要一个 LEFT JOIN 以便结果表有 1 条记录(来自 T1)与最相关的 U_Price，在示例中 21.1$ 与最高 >FromDate 小于 TransactionDate
I need a LEFT JOIN such that the resulting table has 1 record (from T1) with the most relevant U_Price, in the example 21.1$ with the highest FromDate smaller than the TransactionDate
我试过没有成功
SELECT
Item,
TransactionDate
FROM
T1 p
LEFT JOIN T2 o ON o.Item = p.Item
WHERE T2.FromDate>=T1.TransactionDate
一种方法是关联子查询:
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;