基于最高日期和非唯一列的 MySQL JOIN

Question

我需要一些关于我正在处理的 MySQL 查询的帮助。我有如下数据。

表一

id   date1        text     number
---|------------|--------|-------
1  | 2012-12-12 | hi     | 399 
2  | 2011-11-11 | so     | 399
5  | 2010-10-10 | what   | 555
3  | 2009-09-09 | bye    | 300
4  | 2008-08-08 | you    | 300

表2

id   number   date2        ref
---|--------|------------|----
1  | 399    | 2012-06-06 | 40
2  | 399    | 2011-06-06 | 50
5  | 555    | 2011-03-03 | 60

对于表 1 中的每一行，我想从表 2 中获取零个或一个 ref 值。表 1 中的每一行的结果中都应该有一行。两个表的数字列都不是唯一的，因此必须使用 date1 和 date2 列进行连接，其中 date2 是数字的最大值，但不超过该数字的 date1。

上面例子的预期结果应该是这样的。

 date1        text     number   ref
------------|--------|--------|-----
 2012-12-12 | hi     | 399    | 40
 2011-11-11 | so     | 399    | 50
 2010-10-10 | what   | 555    | null
 2009-09-09 | bye    | 300    | null
 2008-08-08 | you    | 300    | null

您可以在结果的第一行中看到，选择了 ref is 40，因为在 table2 中，ref=40 的记录有一个 date2 小于 date1，并且是满足该条件的最高日期。在结果的第二行中，选择了 ref is 50，因为在 table2 中，ref=50 的记录有一个小于 date1 的日期 2，并且是满足该条件的最高日期。其余结果具有空引用，因为 date1 总是小于或 table2 中不存在相应的数字。

我已经到了某个点，但我卡住了。到目前为止，我的查询是这样的。

SELECT date1, text, number, ref
FROM table1
LEFT JOIN (
    SELECT * 
    FROM (
        SELECT * 
        FROM table2
        WHERE date2 <= '2012-12-12'
        ORDER BY date2 DESC
    ) tmp 
    GROUP BY msisdn
) tmp ON table1.number = table2.number;

问题是硬编码日期不行，它应该基于 date1，但我不能使用 date1，因为它在外部查询中。有什么方法可以让这项工作成功吗？

Answer 1

我刚才用不同的表尝试了类似的例子，并且能够得到你想要的。下面是为满足您的需要而修改的类似查询。您可能想要更改 <与 <=如果这就是您要找的。

SELECT a.date1, a.text, b.ref
FROM table1 a LEFT JOIN table2 b ON 
                        ( a.number = b.number
                          AND  a.date1 > b.date2 
                          AND b.date2 = ( SELECT MAX(x.date2) 
                                          FROM table2 x 
                                          WHERE x.number = b.number 
                                             AND x.date2 < a.date1)
                        )