SQL面试题之行转列

 典型的课程表：

mysql> select * from course;
+----+------------+----------+------------+
| id | teacher_id | week_day | has_course |
+----+------------+----------+------------+
|  1 |          1 |        2 | Yes        |
|  2 |          1 |        3 | Yes        |
|  3 |          2 |        1 | Yes        |
|  4 |          3 |        2 | Yes        |
|  5 |          1 |        2 | Yes        |
+----+------------+----------+------------+

对于每个教师的每一天进行判断：

mysql> select teacher_id ,\
    -> ( case week_day when '1' then 'Yes' else '' end ) as 'mon',\
    -> ( case week_day when '2' then 'Yes' else '' end ) as 'tue',\
    -> ( case week_day when '3' then 'Yes' else '' end ) as 'thi',\
    -> ( case week_day when '4' then 'Yes' else '' end ) as 'thu',\
    -> ( case week_day when '5' then 'Yes' else '' end ) as 'fri'
    -> from course\
    -> ;
+------------+-----+-----+-----+-----+-----+
| teacher_id | mon | tue | thi | thu | fri |
+------------+-----+-----+-----+-----+-----+
|          1 |     | Yes |     |     |     |
|          1 |     |     | Yes |     |     |
|          2 | Yes |     |     |     |     |
|          3 |     | Yes |     |     |     |
|          1 |     | Yes |     |     |     |
+------------+-----+-----+-----+-----+-----+

 

这时候就能拿到每一条课程安排对于一周的影响（某一天有没有课）。

可以看到有重复数据，或者说数据比较分散，这时候可以用分组来将数据进行合并：

mysql> select teacher_id ,\
    -> max( case week_day when '1' then 'Yes' else '' end ) as 'mon',\
    -> max( case week_day when '2' then 'Yes' else '' end ) as 'tue',\
    -> max( case week_day when '3' then 'Yes' else '' end ) as 'thi',\
    -> max( case week_day when '4' then 'Yes' else '' end ) as 'thu',\
    -> max( case week_day when '5' then 'Yes' else '' end ) as 'fri'
    -> from course\
    -> group by teacher_id;
+------------+------+------+------+------+------+
| teacher_id | mon  | tue  | thi  | thu  | fri  |
+------------+------+------+------+------+------+
|          1 |      | Yes  | Yes  |      |      |
|          2 | Yes  |      |      |      |      |
|          3 |      | Yes  |      |      |      |
+------------+------+------+------+------+------+

 

要点：

不论是列转行还是行转列，转过去的目标肯定是被“造出来”的（case...when）；

利用分组函数去重和合并。