【MySQL查询常见面试题】MySQL行转列、列转行

1. 列转行

表结构

mysql> desc row_to_column;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(255) | YES  |     | NULL    |       |
| score   | varchar(255) | YES  |     | NULL    |       |
| subject | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

表数据

mysql> select * from row_to_column;
+------+-------+---------+
| name | score | subject |
+------+-------+---------+
| 小明 | 80    | 语文    |
| 小明 | 90    | 数学    |
| 小明 | 85    | 英语    |
| 小红 | 70    | 语文    |
| 小红 | 95    | 数学    |
| 小红 | 85    | 英语    |
+------+-------+---------+

实现SQL

mysql> select name, max(if(subject = '语文', score, null)) as '语文' ,max(if(subject = '数学', score, null)) as '数学',max(if(subject = '英语', score, null)) as '英语' from row_to_column group by name;  
+------+------+------+------+
| name | 语文 | 数学 | 英语 |
+------+------+------+------+
| 小明 | 80   | 90   | 85   |
| 小红 | 70   | 95   | 85   |
+------+------+------+------+

2. 行转列

表结构

mysql> desc column_to_row;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(255) | YES  |     | NULL    |       |
| subject1 | varchar(255) | YES  |     | NULL    |       |
| subject2 | varchar(255) | YES  |     | NULL    |       |
| subject3 | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

表数据

mysql> select * from column_to_row;
+------+----------+----------+----------+
| name | subject1 | subject2 | subject3 |
+------+----------+----------+----------+
| 小明 | 80       | 90       | 100      |
| 小红 | 100      | 90       | 80       |
+------+----------+----------+----------+

实现SQL

mysql> select name, 'subject1' as 'subject', subject1 as score from column_to_row
    -> union all
    -> select name, 'subject2' as 'subject', subject2 as score from column_to_row
    -> union all
    -> select name, 'subject3' as 'subject', subject3 as score from column_to_row
    -> ;
+------+----------+-------+
| name | subject  | score |
+------+----------+-------+
| 小明 | subject1 | 80    |
| 小红 | subject1 | 100   |
| 小明 | subject2 | 90    |
| 小红 | subject2 | 90    |
| 小明 | subject3 | 100   |
| 小红 | subject3 | 80    |
+------+----------+-------+