LEFT JOIN 具有特定条件 [英] LEFT JOIN with specific criteria

问题描述

在 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 <= t1.transactiondate按 t2.fromdate desc 排序) 作为 u_price从 t1;

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;

这篇关于LEFT JOIN 具有特定条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！