join on max mysql_Left Join on MAX(DATE)

问题

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.

Table a contains a history of article transactions:

Store_type, Date, Article, ...

Table b contains a history of article prices:

Store_type, Date, Article, price

Currently i have this:

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

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?

回答1:

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.

来源：https://stackoverflow.com/questions/32265830/left-join-on-maxdate