课程行转列
数据准备：
id course
1,a
1,a
1,b
1,c
1,e
2,a
2,c
2,d
2,f
3,a
3,b
3,c
3,e
创建表：
create table table09(id int,course string)
row format delimited fields terminated by ',';
加载数据：
load data local inpath '/路径/test09.txt' into table table09;
查询数据映射情况：
select * from t1;
+-------------+-----------------+
| table09.id | table09.course |
+-------------+-----------------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | e |
| 2 | a |
| 2 | c |
| 2 | d |
| 2 | f |
| 3 | a |
| 3 | b |
| 3 | c |
| 3 | e |
+-------------+-----------------+
分析：
想要的结果
方式一：
语法:
case 参数 when 'a' then 1 else 0 end
select id,
sum(case course when 'a' then 1 else 0 end) a,
sum(case course when 'b' then 1 else 0 end) b,
sum(case course when 'c' then 1 else 0 end) c,
sum(case course when 'd' then 1 else 0 end) d,
sum(case course when 'e' then 1 else 0 end) e,
sum(case course when 'f' then 1 else 0 end) f
from table09 group by id ;
结果：
+-----+----+----+----+----+----+----+
| id | a | b | c | d | e | f |
+-----+----+----+----+----+----+----+
| 1 | 2 | 1 | 1 | 0 | 1 | 0 |
| 2 | 1 | 0 | 1 | 1 | 0 | 1 |
| 3 | 1 | 1 | 1 | 0 | 1 | 0 |
+-----+----+----+----+----+----+----+
方式二：
语法：
if(字段='数据',1,0) 判断条件成立结果为1,不成立结果为0
select id,
sum(if(course='a',1,0)) as a,
sum(if(course='b',1,0)) as b,
sum(if(course='c',1,0)) as c,
sum(if(course='d',1,0)) as d,
sum(if(course='e',1,0)) as e,
sum(if(course='f',1,0)) as f
from table09
group by id;
结果：
+-----+----+----+----+----+----+----+
| id | a | b | c | d | e | f |
+-----+----+----+----+----+----+----+
| 1 | 2 | 1 | 1 | 0 | 1 | 0 |
| 2 | 1 | 0 | 1 | 1 | 0 | 1 |
| 3 | 1 | 1 | 1 | 0 | 1 | 0 |
+-----+----+----+----+----+----+----+