mysql - 根据日期加入表

Question

我有两个表，一个是公司列表，另一个是公司发布的职位列表，其中包含申请截止日期和开始日期。

有些公司没有招聘职位，有些公司只有截止日期已过的职位，有些公司只有在职职位，而其他公司则有过去和有效的申请。

我希望能够作为查询结果显示的是所有公司的列表，它们具有最近的截止日期，并按该截止日期排序。所以结果可能看起来像这样(如果今天是 2015 年 1 月 1 日)。

对不起，我说错了。我想要做的是找到下一个 future 的截止日期，如果没有 future 的截止日期，则显示过去的最后一个截止日期。因此，在下面的第一个表格中，BillyCo 截止日期已过，但显示了下一个 BuffyCo 截止日期。在 BillyCo 案例中有更早的截止日期，但在 BuffyCo 案例中有更早和更晚的截止日期。

id   name     title     date
==   ====     =====     ====
1    BobCo    null      null
2    BillCo   Designer  2014-12-01
3    BuffyCo  Admin     2015-01-31

因此，BobCo 根本没有列出工作，BillCo 的截止日期已过，而 BuffyCo 的截止日期在未来。

有问题的部分是 BillCo 可能有一组这样的工作:

id   title     date        desired hit
==   =====     ====        ===========
1    Coder     2013-12-01
2    Manager   2014-06-30
3    Designer  2012-12-01  <--

BuffyCo 可能有:

id   title     date        desired hit
==   =====     ====        ===========
1    Magician  2013-10-01
2    Teaboy    2014-05-19
3    Admin     2015-01-31  <--
4    Writer    2015-02-28

所以，我可以这样做:

select * from (
    select * from firms
    left join jobs on firms.id = jobs.firmid
    order by date desc) 
as t1 group by firmid;

或者，通过日期标准限制加入或返回的作业，但我似乎无法获得我想要返回的记录。即上面的查询将返回:

id   name     title     date
==   ====     =====     ====
1    BobCo    null      null
2    BillCo   Designer  2014-12-01
3    BuffyCo  Writer    2015-02-28

对于 BuffyCo，它返回的是 Writer 作业而不是 Admin 作业。

用 SQL 查询是不可能的吗？感谢任何建议，提前致谢。

Answer 1

我想这可能是你需要的，你需要:

1) calculate the delta for all of your jobs between the date and the  current date finding the min delta for each firm.
2) join firms to jobs only on where firm id's match and where the calculated min delta for the firm matches the delta for the row in jobs.

    SELECT f.id, f.name, j.title,j.date
    FROM firms f LEFT JOIN 
        (SELECT firmid,MIN(abs(datediff(date, curdate())))) AS delta 
         FROM jobs 
         GROUP BY firmid) d 
    ON f.id = d.firmid 
    LEFT JOIN jobs j ON f.id = j.id AND d.delta = abs(datediff(j.date, curdate())))) ;