求助各位大神,怎么把列数据换成行数据呢？

[mv110335]

怎么样才能处理这样的结果呢？

[qq_32711805]

手机不好码代码，望采纳

[qq_32711805]

mysql> select * from score;
+----+------+------+-------+-------+
| id | name | year | month | score |
+----+------+------+-------+-------+
| 1 | 张三 | 2017 | 2 | 36 |
| 2 | 张三 | 2017 | 3 | 98 |
| 3 | 张三 | 2017 | 4 | 22 |
| 4 | 张三 | 2018 | 1 | 40 |
| 5 | 张三 | 2018 | 2 | 60 |
| 6 | 张三 | 2018 | 3 | 50 |
| 7 | 张三 | 2018 | 4 | 80 |
| 8 | 张三 | 2017 | 1 | 25 |
+----+------+------+-------+-------+


---------------------------------------------------

mysql> select name,
-> year,
-> max(case month when '1' then score else 0 end) '1',
-> max(case month when '2' then score else 0 end) '2',
-> max(case month when '3' then score else 0 end) '3',
-> max(case month when '4' then score else 0 end) '4'
-> from score
-> group by name,year
-> order by year desc;
+------+------+------+------+------+------+
| name | year | 1 | 2 | 3 | 4 |
+------+------+------+------+------+------+
| 张三 | 2018 | 40 | 60 | 50 | 80 |
| 张三 | 2017 | 25 | 36 | 98 | 22 |
+------+------+------+------+------+------+

[学海无涯-回头是岸]

百度一下 行转列 很多的

[DB丨JOK]

自行百度 WM_CONCAT 函数

[碧水幽幽泉]

以下SQL可以实现你的需求:

create table tmp as
select '2017' c1, 1 c2,3100 c3 from dual union all
select '2017' c1, 2 c2,3200 c3 from dual union all
select '2017' c1, 3 c2,3300 c3 from dual union all
select '2017' c1, 4 c2,3400 c3 from dual union all
select '2018' c1, 1 c2,4100 c3 from dual union all
select '2018' c1, 2 c2,4200 c3 from dual union all
select '2018' c1, 3 c2,4300 c3 from dual union all
select '2018' c1, 4 c2,4400 c3 from dual;

select c1 as "年",
           max(decode(c2,1,c3,null )) as "一月",
           max(decode(c2,2,c3,null )) as "二月",
           max(decode(c2,3,c3,null )) as "三月",
           max(decode(c2,4,c3,null )) as "四月"
   from tmp
   group by c1