在MAX(DATE)左加入 [英] Left Join on MAX(DATE)

问题描述

我有2张桌子:交易(a)和价格(b).我想从表b检索在交易日期有效的价格.

I have 2 tables: Transactions (a) and Prices (b). I want to retrieve the price from table b that is valid on the transaction date.

表a包含商品交易记录: Store_type，日期，文章...

Table a contains a history of article transactions: Store_type, Date, Article, ...

表b包含商品价格的历史记录: 商店类型，日期，商品，价格

Table b contains a history of article prices: Store_type, Date, Article, price

目前我有这个:

Select
a.Store_type,
a.Date
a.Article,
(select b.price
  from PRICES b
  where b.Store_type = a.Store_type
  and b.Article = a.Article
  and b.Date = (select max(c.date)
    from PRICES c
    where c.Store_type = a.Store_type
    and c.Article = a.Article
    and c.date <= a.date)) AS ART_PRICE
from TRANSACTIONS a

它工作得很好，但是由于使用了double子查询，它似乎花费了很长时间. 可以通过LEFT JOIN进行同样的操作吗?

It works just fine but it seems to take very long because of the double subquery. Can the same be done with a LEFT JOIN?

推荐答案

可以尝试使用以下查询吗?

Can try using the below query ?

SELECT      a.Store_type, a.Date, a.Article, b.Price
FROM        TRANSACTIONS a
LEFT JOIN   PRICES b ON a.Store_type = b.Store_type
AND         a.Article = b.Article
AND         b.Date = (SELECT   MAX (c.Date) 
                      FROM     PRICES c 
                      WHERE    a.Store_type = c.Store_Type
                      AND      a.Article = c.Article
                      AND      c.Date <= a.Date)

尽管它仍然有一个子查询，用于检索最大日期.

It still has one subquery though, used to retrieve the maximum date.

这篇关于在MAX(DATE)左加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！