习题描述
表名:t1(源表)
表结构
a -- 年份
b -- 部门
c -- 绩效得分
表内容
+--------+--------+--------+--+
| a | b | c |
+--------+--------+--------+--+
| 2014 | B | 9 |
| 2015 | A | 8 |
| 2014 | A | 10 |
| 2015 | B | 7 |
+--------+--------+--------+--+
问题1
请用 SQL 实现将上述表内容转为如下输出结果所示:
+-------+--------+--------+--+
| a | col_A | col_B |
+-------+--------+--------+--+
| 2014 | 10 | 9 |
| 2015 | 8 | 7 |
+-------+--------+--------+--+
问题2
请用 SQL 实现将上述问题的结果转成源表,上述结果表名为 t1_2。
实现
问题1
with t1 as(
select 2014 as a, 'B' as b, 9 as c union all
select 2015 as a, 'A' as b, 8 as c union all
select 2014 as a, 'A' as b, 10 as c union all
select 2015 as a, 'B' as b, 7 as c
)
select
a,
--聚合函数实现,max,sum都可
max(case when b = 'A' then c end) as col_A,
max(case when b = 'B' then c end) as col_B
from t1
group by a;
行转列问题。
问题2
with t1_2 as(
select 2014 as a, 10 as col_A, 9 as col_B union all
select 2015 as a, 8 as col_A, 7 as col_B
)
select a, 'A' as b, col_A as c from t1_2
union all
select a, 'B' as b, col_B as c from t1_2
;
列转行问题。