mysql 日期连接,在MySQL中过去最近的日期连接表

I have a sqlite query that I'm trying to write. I have two tables:

TableA (sales):

id

sales

date

TableB (goals):

id

goal

date

I'm selecting from TableA like this:

SELECT id,sales,date FROM TableA

Now to the "tricky" part. I need to join TableB to the query because I need the goal field in TableB for each row in TableA. TableB only contains goals for some dates, while TableA contains all dates. So I can't just use TableA.date = TableB.date

Instead, for each row in TableA I need to take the goal from TableB on the date nearest in the past to the date in TableA. Hope I was able to explain what I needed. Can't figure out how to do it..

解决方案SELECT a.id, a.sales, a.date, (SELECT TOP 1 Goal

FROM TableB b WHERE b.date < a.date

ORDER BY b.date DESC) As Goal

FROM TableA a

Going off the nearest date in the past.