时间戳

首页 > 解决方案 > MySQL - 加入和条件选择列

mysql - MySQL - 加入和条件选择列

问题描述

不知道如何准确地命名问题。因此我的示例表：

人们：

id_people | peopleName |
------------------------
1         | John       |
2         | Jane       |
3         | Frank      |
4         | Emma       |

培训班：

id | id_course | id_people | date       |
-----------------------------------------
1  | 1         | 1         | 2011-02-14 |
2  | 2         | 1         | 2013-05-01 |
3  | 3         | 1         | 2015-08-25 |
4  | 1         | 2         | 2012-01-05 |
5  | 1         | 4         | 2017-10-10 |

现在我想生成一个表格，其中每人有一行，每门课程都有一列，日期如下：

id_people | peopleName | Course1    | Course2    | Course3    |
---------------------------------------------------------------
1         | John       | 2011-02-14 | 2013-05-01 | 2015-08-25 |
2         | Jane       | 2012-01-05 |            |            |
3         | Frank      |            |            |            |
4         | Emma       | 2017-10-10 |            |            |

我尝试了以下查询：

SELECT *,
 max(case when courses.id_course = 1 then cource.date end) as Course1,
 max(case when courses.id_course = 2 then cource.date end) as Course2,
 max(case when courses.id_course = 3 then cource.date end) as Course3  
FROM people
JOIN courses ON people.id_people = courses.id_people
GROUP BY people.id_people

但是我收到了错误 #1055（[..] 这与 sql_mode=only_full_group_by 不兼容）。因此，它迫使我按声明将列 course.id、course.id_course 和 course.date 添加到组中。这会导致每人每门课程排成一行。如何使用 sql 语句获得我想要的结果表？我无权更改 sql_mode 变量。

标签： mysqlsqljoin

解决方案

您尚未定义id_people为 aprimary key或至少unique. 你应该这样做。

同时，您可以先聚合，然后join：

SELECT *
FROM people p JOIN
     (SELECT c.id_people
             max(case when c.id_course = 1 then c.date end) as Course1,
             max(case when c.id_course = 2 then c.date end) as Course2,
             max(case when c.id_course = 3 then c.date end) as Course3  
      FROM courses c
      GROUP BY c.id_people
     ) c
     USING (id_people);

推荐阅读