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    |+------+----------+-------+