MySQL 5.1.7 两个表的条​​件联接从第二个表中按日期选择顶部记录以及第一个表中的所有记录

Question

我有两个表:

• 说明 — 每个人都有一份独特的记录
• 事件 — 每个人有多个记录；记录有多种类型

我需要检索完整的指令列表，以及每个指令的具有最大(最新)日期的'appointment'事件。

我尝试过group by，但未能获得正确的结果。

说明表:

    id    first_name    surname    telephone
   ----  ------------  ---------  -----------
    1     bob           marley     555-1234
    2     steve         pike       555-3456
    3     daniel        osborne    555-9876
    4     mark          hodge      555-6600
    5     stefan        belfant    555-8080

事件表:

    id    instruction_id    type        commment    event_date
   ----- ----------------  ------------ ---------  ------------
    1     1                 create      na          2013-11-18
    2     2                 call        na          2013-11-19
    3     2                 appointment onsite      2013-12-02
    4     3                 create      na          2013-09-17
    5     3                 appointment office      2013-09-17
    6     3                 finalize    as discuss  2013-11-19
    7     4                 create      na          2013-12-02
    8     4                 cancel      na          2013-12-02
    9     5                 create      na          2013-10-02 
    10    1                 appointment at home     2013-11-22 
    11    2                 appointment at home     2013-12-05

所需的输出:

    id   first_name     surname     telephone   appointment   comment
   ---- ------------   ---------   ----------- ------------- ---------
    1    bob            marley      555-1234    2013-11-22    at home  
    2    steve          pike        555-3456    2013-12-05    at home
    3    daniel         osborne     555-9876    2013-09-17    office
    4    mark           hodge       555-6600
    5    stefan         belfant     555-6060

Answer 1

假设任何特定人员的最新/最大约会日期都是唯一的，您可以编写:

SELECT instructions.*,
       latest_events.event_date AS appointment,
       latest_events.comment
  FROM instructions
  LEFT
 OUTER
  JOIN ( SELECT events.instruction_id,
                MAX(events.event_date) AS latest_event_date
           FROM events
          WHERE events.type = 'appointment'
          GROUP
             BY events.instruction_id
       ) latest_event_dates
    ON instructions.id = latest_event_dates.instruction_id
  LEFT
 OUTER
  JOIN events latest_events
    ON latest_event_dates.instruction_id = latest_events.instruction_id
   AND latest_event_dates.latest_event_date = latest_events.event_date
   AND 'appointment' = latest_events.type
;

(如果最新/最好的约会日期不是唯一的，那么您需要在连接中添加另一个步骤，在其中选择 MIN(events.id) 或 MAX(events.id) 或给定的 events.instruction_id 和 events.event_date 等。)