mysql - SQL 查询 - 在小于或等于日期加入

Question

我无法弄清楚如何格式化以下查询。我想获取 Table A 并左连接 Table B。不仅在 a.country = b.country 上，而且在小于等于 join_date 的 max start_date 上。

Postgres(红移)

表A

  ID      join_date     country          email     
 -----    -----------   ---------        ------
  124     '2013-10-03'     US         john@doe.com
  423     '2013-04-21'     CA         bob@doe.com
  412     '2013-03-30'     US         test@test.com

表B

  start_date        country          joined_from
-------------     -----------       --------------
'2013-08-21'          US                google
'2014-01-02'          CA                yahoo
'2013-03-02'          CA                microsoft
'2013-02-10'          US                facebook
'2013-09-01'          US                yahoo

最终结果

  ID     join_date     country      email         start_date     joined_from
------  -----------    ---------   ---------     ------------   -------------
 124    '2013-10-03'     US        john@doe.com   '2013-09-01'     yahoo
 423    '2013-04-21'     CA        bob@doe.com    '2013-03-02'    microsoft
 412    '2013-03-30'     US        test@test.com  '2013-02-10'    facebook

Answer 1

不漂亮，但是将您的条件直接放在 JOIN 中应该可行:

SELECT a.ID, a.join_date, a.country, a.email, b.start_date, b.joined_from
FROM a LEFT JOIN b ON a.country = b.country 
      AND b.start_date = (
          SELECT MAX(start_date) FROM b b2 
          WHERE b2.country = a.country AND b2.start_date <= a.join_date
      );