mysql - 连接两个表并按类型创建虚拟列

Question

我目前正在为学者考试开发某种日历。由于日历目前非常慢，我想增强它背后的 MySQL。我已阅读有关 JOIN 的信息并尝试使用它们以直接获得我需要的结果，但我目前遇到了一个问题。

我有两个表(简化)

DAYS                           EVENTS
id | date                      id | day_id | class | text
1  | 2016-02-05                1  | 1      | a     | Example
2  | 2016-02-06                2  | 1      | b     | Example for Class b
etc.

现在，如果我选择一天，我想收到以下结果:

[0] => Array
    (
        [id] => 1
        [date] => 2016-01-25
        [a] => 1 (ID of the Event on that day with the class 'a')
        [b] => 2 (ID of the Event on that day with the class 'b')
        [c] => NULL
    )

但是到目前为止我创建的查询没有合并结果:

[0] => Array
    (
        [id] => 1
        [date] => 2016-02-05
        [a] => 13
        [b] => 
        [c] => 
    )

[1] => Array
    (
        [id] => 11
        [date] => 2016-02-05
        [a] => 
        [b] => 14
        [c] => 
    )

如何合并这两个结果？我的查询如下所示:

SELECT d.*, 
     CASE WHEN e.class = 'a' THEN e.id ELSE NULL END AS a,
     CASE WHEN e.class = 'b' THEN e.id ELSE NULL END AS b,
     CASE WHEN e.class = 'c' THEN e.id ELSE NULL END AS c
  FROM examplan_days d
      LEFT JOIN examplan_events e ON d.id = e.day_id

Answer 1

我想你想要聚合:

SELECT d.*, 
       MAX(CASE WHEN e.class = 'a' THEN e.id END) AS a,
       MAX(CASE WHEN e.class = 'b' THEN e.id END) AS b,
       MAX(CASE WHEN e.class = 'c' THEN e.id END) AS c
FROM examplan_days d LEFT JOIN
     examplan_events e
     ON d.id = e.day_id
GROUP BY d.id;

如果您在给定的一天可以上多节课，那么您应该使用 GROUP_CONCAT() 而不是 MAX()。