在MySQL中过去最近的日期连接表 [英] Join tables on nearest date in the past, in MySQL

问题描述

我有一个我试图写的sqlite查询。我有两张表：

TableA（sales）：
id
sales
date

TableB（goals）：
id
goal
date

我从TableA中选择如下：
SELECT id，sales，date FROM TableA

现在到棘手部分。我需要加入TableB来查询，因为我需要TableA中的每个行的TableB 中的目标字段。 TableB仅包含某些日期的目标，而 TableA包含所有日期。所以我不能仅仅使用TableA.date = TableB.date $ /

相反，对于TableA中的每一行，我需要从最近的过去到表A中的日期。希望能够解释我需要什么。不能弄清楚怎么做..

解决方案

  SELECT a.id， a.sales，a.date，（SELECT TOP 1 Goal 
 FROM TableB b WHERE b.date< a.date 
 ORDER BY b.date DESC）作为目标
 FROM TableA a 
  

离开过去最近的日期。

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.

这篇关于在MySQL中过去最近的日期连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！