MySQL JOIN 表基于主表中的 MAX(date) 和连接表中的 MAX(id) 与 LIMIT

Question

如果标题没有任何意义，这就是我需要做的简而言之。我需要在主表中“按日期”选择最近的 X 条记录，然后加入属于的数据通过在连接表中“按 id”选择最近的记录来查看这些记录。

这是一些示例输出..

表:lead_unique(此表中只有唯一的 ssn)

+-----------+--------------+| ssn       | created_date |+-----------+--------------+| 111111111 | 2015-03-01   || 999999999 | 2015-03-03   || 555555555 | 2015-02-08   |+-----------+--------------+

表:lead_data

+----+-----------+-------+----------------+-------------+-------+-------+| id | ssn       | name  | address        | city        | state | zip   |+----+-----------+-------+----------------+-------------+-------+-------+|  1 | 111111111 | Bob1  | 1234 Test Ln   | Mound       | CA    | 55555 ||  2 | 111111111 | Bob2  | 1234 Test Ln   | Mound       | CA    | 55555 ||  3 | 999999999 | Jane1 | 5432 Lola Blvd | Patton      | NJ    | 33333 ||  4 | 999999999 | Jane2 | 5432 Lola Blvd | Patton      | NJ    | 33333 ||  5 | 555555555 | Jack1 | 832 92nd Ave N | Bright View | AL    | 88888 ||  6 | 999999999 | Jane3 | 5432 Lola Blvd | Patton      | NJ    | 33333 |+----+-----------+-------+----------------+-------------+-------+-------+

期望的输出(可以是 asc/desc 日期列，无关紧要)

+--------------+-----------+-------+| created_date | ssn       | name  |+--------------+-----------+-------+| 2015-03-03   | 999999999 | Jane3 || 2015-03-01   | 111111111 | Bob2  || 2015-02-08   | 555555555 | Jack1 |+--------------+-----------+-------+

期望的输出(限制 2)

+--------------+-----------+-------+| created_date | ssn       | name  |+--------------+-----------+-------+| 2015-03-03   | 999999999 | Jane3 || 2015-03-01   | 111111111 | Bob2  |+--------------+-----------+-------+

查询可能类似于以下内容，但我也可能会离开，因为我在这里寻求帮助但运气不好..

SELECT       lead_unique.created_date,     lead_unique.ssn,    lead_data.nameFROM          lead_uniqueJOIN          (        SELECT                ...        FROM                  lead_data        ...    ) lead_data         ......LIMIT 2

我之前只用过一次堆栈溢出，所以如果我还有什么可以补充的，请告诉我!谢谢!!

Answer 1

我倾向于对数据片段使用相关子查询——你的问题只提到一列:

select u.created_date, u.ssn,
       (select d.name
        from lead_data d
        where d.ssn = u.ssn
        order by d.id desc
        limit 1
       ) as name
from lead_unique u
order by u.created_date desc
limit 2;

实际上，出于性能原因，我会将唯一组件放在子查询中:

select u.created_date, u.ssn,
       (select d.name
        from lead_data d
        where d.ssn = u.ssn
        order by d.id desc
        limit 1
       ) as name
from (select u.*
      from lead_unique u
      order by u.created_date desc
      limit 2
     ) u;

编辑:

即使有多个列，最高效的方法可能仍然是使用子查询:

select created_date, ssn, d.*
from (select u.created_date, u.ssn,
             (select d.id
              from lead_data d
              where d.ssn = u.ssn
              order by d.id desc
              limit 1
             ) as id
      from (select u.*
            from lead_unique u
            order by u.created_date desc
            limit 2
           ) u
     ) u join
     lead_data d
     on u.id = d.id;

顺便说一句，如果性能是个问题，您需要以下索引:lead_unique(created_date) 和 lead_data(id)。这两个索引应该很快。