MySQL-在JOIN中选择给定日期之前的最新记录 [英] MySQL - Selecting the latest records before a given date in a JOIN

问题描述

与我同在，我是SQL的新手，在解释这个问题时遇到了麻烦...

Bear with me, I'm new to SQL and am having trouble explaining this...

我有两个类似于以下内容的表:

I have two tables similar to the following:

test1
+------+----------+----------+-----------+
|  id  |  serial  |  t1data  |  t1date   |
+------+----------+----------+-----------+
|  1   |    a     |   ...    |  6/02/12  |
|  2   |    a     |   ...    |  6/04/12  |
|  3   |    b     |   ...    |  6/06/12  |
|  4   |    a     |   ...    |  6/08/12  |
+------+----------+----------+-----------+

test2
+------+----------+----------+-----------+
|  id  |  serial  |  t2data  |  t2date   |
+------+----------+----------+-----------+
|  1   |    a     |   ...    |  6/05/12  |
|  2   |    b     |   ...    |  6/07/12  |
|  3   |    b     |   ...    |  6/08/12  |
|  4   |    a     |   ...    |  6/09/12  |
+------+----------+----------+-----------+

我想创建一个查询以连接两个表，以便test2中的每个记录对应于test2日期之前发生的最新test1记录.任何给定的序列在任何一个表中都可以有多个记录.

I would like to create a query to join the two tables so that each record in test2 corresponds to latest test1 record that occurred prior to the date of test2. Any given serial can have multiple records in either table.

使用以上示例，结果应如下所示:

Using the above example the result should look like this:

+----------+----------+-----------+----------+-----------+
|  serial  |  t2data  |  t2date   |  t1data  |  t1date   |
+----------+----------+-----------+----------+-----------+
|    a     |   ...    |  6/05/12  |   ...    |  6/04/12  |
|    b     |   ...    |  6/07/12  |   ...    |  6/06/12  |
|    b     |   ...    |  6/08/12  |   ...    |  6/06/12  |
|    a     |   ...    |  6/09/12  |   ...    |  6/08/12  |
+----------+----------+-----------+----------+-----------+

这是我尝试过的查询，但它不起作用.

This is the query I tried but it does not work.

SELECT 
    t2.serial, t2.t2data, t2.t2date, t1a.t1data, t1a.t1date
FROM 
    test2 t2
JOIN 
    test1 t1a ON (t1a.serial = t2.serial AND t1a.t1date > t2.t2date)
LEFT JOIN 
    test1 t1b ON (t1a.serial = t1b.serial AND t1a.t1date < t1b.t1date)
WHERE 
    t1b.serial IS NULL

我可能完全错了，但是我该怎么做才能使这项工作成功?

I may be going at this all wrong but what can I do to make this work?

推荐答案

我不确定这是否是最好的方法，但它是否有效:

I'm not sure if this is the best approach, but it's working:

SELECT t2.serial, t2.t2data, t1.t1data, t1.t1date
FROM
  (SELECT t2.serial, t2.t2data, t2.t2date,
         (SELECT max(t1date)
            FROM test1 t1
           WHERE t1.serial = t2.serial
             AND t1.t1date <= t2.t2date) AS t1date
  FROM test2 t2) t2
  LEFT OUTER JOIN
   test1 t1 ON t2.serial = t1.serial AND t2.t1date = t1.t1date;

http://www.sqlfiddle.com/#!2/215c263/2

这篇关于MySQL-在JOIN中选择给定日期之前的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！