数据库行转列和列转行小例子
16lz
2021-01-22
有时候，我们想从另一个角度看一张表。这时候就会涉及行列的转换。假如有一张成绩表
mysql> select * from scores;
+------+----------+-------+
| name | kemu | score |
+------+----------+-------+
| A | chinese | 90 |
| A | math | 96 |
| A | english | 79 |
| A | computer | 89 |
| B | computer | 93 |
| B | english | 92 |
| C | english | 77 |
| C | chinese | 78 |
+------+----------+-------+
现在我们想看下A同学的语文，数学，英语分数，希望能列在一行里。这时我们就需要把行转成列。 我们使用case when 语句
select name,sum(case kemu when 'chinese' then score end) as chinese, sum(case kemu when 'math' then score end) as math, sum(case kemu when 'english' then score end) as english, sum(case kemu when 'computer' then score end) as computer from scores group by name;
+------+---------+------+---------+----------+
| name | chinese | math | english | computer |
+------+---------+------+---------+----------+
| A | 90 | 96 | 79 | 89 |
| B | NULL | NULL | 92 | 93 |
| C | 78 | NULL | 77 | NULL |
+------+---------+------+---------+----------+
列转行 假如有一张成绩表为 mysql> select * from scores_name;
+------+---------+------+---------+----------+
| name | chinese | math | english | computer |
+------+---------+------+---------+----------+
| A | 90 | 96 | 79 | 89 |
| B | NULL | NULL | 92 | 93 |
| C | 78 | NULL | 77 | NULL |
+------+---------+------+---------+----------+
3 rows in set (0.00 sec)
我们将它行转列,使用union all select name,'chinese' as kemu,chinese as score from scores_name union all select name,'math' as kemu,math as score from scores_name union all select name,'english' askemu, english as score from scores_name union all select name,'computer' as kemu,computer asscore from scores_name
+------+----------+-------+
| name | kemu | score |
+------+----------+-------+
| A | chinese | 90 |
| B | chinese | NULL |
| C | chinese | 78 |
| A | math | 96 |
| B | math | NULL |
| C | math | NULL |
| A | english | 79 |
| B | english | 92 |
| C | english | 77 |
| A | computer | 89 |
| B | computer | 93 |
| C | computer | NULL |
+------+----------+-------+
12 rows in set (0.00 sec)
+------+----------+-------+
| name | kemu | score |
+------+----------+-------+
| A | chinese | 90 |
| A | math | 96 |
| A | english | 79 |
| A | computer | 89 |
| B | computer | 93 |
| B | english | 92 |
| C | english | 77 |
| C | chinese | 78 |
+------+----------+-------+
现在我们想看下A同学的语文，数学，英语分数，希望能列在一行里。这时我们就需要把行转成列。 我们使用case when 语句
select name,sum(case kemu when 'chinese' then score end) as chinese, sum(case kemu when 'math' then score end) as math, sum(case kemu when 'english' then score end) as english, sum(case kemu when 'computer' then score end) as computer from scores group by name;
+------+---------+------+---------+----------+
| name | chinese | math | english | computer |
+------+---------+------+---------+----------+
| A | 90 | 96 | 79 | 89 |
| B | NULL | NULL | 92 | 93 |
| C | 78 | NULL | 77 | NULL |
+------+---------+------+---------+----------+
列转行 假如有一张成绩表为 mysql> select * from scores_name;
+------+---------+------+---------+----------+
| name | chinese | math | english | computer |
+------+---------+------+---------+----------+
| A | 90 | 96 | 79 | 89 |
| B | NULL | NULL | 92 | 93 |
| C | 78 | NULL | 77 | NULL |
+------+---------+------+---------+----------+
3 rows in set (0.00 sec)
我们将它行转列,使用union all select name,'chinese' as kemu,chinese as score from scores_name union all select name,'math' as kemu,math as score from scores_name union all select name,'english' askemu, english as score from scores_name union all select name,'computer' as kemu,computer asscore from scores_name
+------+----------+-------+
| name | kemu | score |
+------+----------+-------+
| A | chinese | 90 |
| B | chinese | NULL |
| C | chinese | 78 |
| A | math | 96 |
| B | math | NULL |
| C | math | NULL |
| A | english | 79 |
| B | english | 92 |
| C | english | 77 |
| A | computer | 89 |
| B | computer | 93 |
| C | computer | NULL |
+------+----------+-------+
12 rows in set (0.00 sec)