mysql - SQL - 从多个表中选择多个字段

Question

想象一下:

表1

ID  Type    Priority
1212    A   CRITICAL
1213    B   MAJOR
1214    B   MINOR
1215    A   MAJOR
1216    A   CRITICAL
1217    A   CRITICAL

表2

ID         STATE        CHANGEDATE
1212       Pending      03-06-2015 17:47
1212       Closed       04-06-2015 05:47
1212       InProgress   03-06-2015 15:32
1212       Start        03-06-2015 15:07
1212       Opened       03-06-2015 13:47

• table1 包含所有ID的信息
• table2包含所有个人ID的信息

我需要的是:我需要从两个表中获取所有 ID 的信息。

为什么我在挣扎:除了我的 n00b 级别(可以说我还不是 Valhalla Material )，我无法将第一个表中的信息与第二个表中的信息相关联。

我的想象:

ID   Type   Priority    Opened              Closed
1212    A   CRITICAL    03-06-2015 13:47    -
1215    A   MAJOR       06-06-2015 18:00    07-06-2015 18:00
1216    A   CRITICAL    03-05-2015 13:10    04-06-2015 18:00
1217    A   CRITICAL    01-06-2015 11:03    05-06-2015 18:00

更新:

这是我尝试过的，但专注于一个特定的 ID (1212):

SELECT
  MAX (ID_A) AS "ID",
  MAX (STATE_A) AS "ACTION NAME",
  MAX (CHANGEDATE_A) AS "START",
  MAX (STATE_B) AS "ACTION NAME",
  MAX (CHANGEDATE_B) AS "END"
FROM (
SELECT
  ID AS ID_A,
  STATE AS STATE_A,
  CHANGEDATE AS CHANGEDATE_A,
  NULL AS ID_B,
  NULL AS STATE_B,
  NULL AS CHANGEDATE_B
FROM table2
WHERE table2.ID = '1212' AND table2.STATE = 'Start'
UNION ALL
SELECT
  NULL AS ID_A,
  NULL AS STATE_A,
  NULL AS CHANGEDATE_A,
  ID AS ID_B,
  STATE AS STATE_B,
  CHANGEDATE AS CHANGEDATE_B
FROM table2
WHERE table2.ID = '1212' AND table2.STATE = 'Close');

运行这个我会得到以下结果:

ID   ACTION NAME    START         ACTION NAME   END
1212    Start   03-06-2015 13:47    Close        -

我需要的是相同的，但这次为所有 ID 生成一个列表(不指定 ID)。

Answer 1

select  t1.ID,
        t1.Type,
        t1.Priority,
        max(case when t2.State = 'Opened' then ChangeDate end) as Opened,       
        max(case when t2.State = 'Closed' then ChangeDate end) as Closed
from    table1 t1 join table2 t2 on t1.id = t2.id
group by t1.ID,
         t1.Type,
         t1.Priority