MySQL和sparkSQL合并行

表A

表B

从表A到表B

MySQL 写法：select name, group_concat(score seperate ';') as score from A group by name

spark 写法：df.groupBy('name').agg(collect_list('score').alias('score')).show()//还可以使用collect_set去重

 

表C

从表A到表C

select name,
    max(case type when '数学' then score else 0 end) as math,
    max(case type when '英语' then score else 0 end) as english,
    max(case type when '语文' then score else 0 end) as chinese
from A group by name