MySQL和Hive中的行转列、列转行

水善利万物而不争，处众人之所恶，故几于道💦

MySQL

1.行转列

把多行转成列。直接group，sum(if())

2.列转行

Hive

1.行转列

select name,sum(if(km='shuxue',cj,0)) shuxue,sum(if(km='yuwen',cj,0)) yuwen,sum(if(km='huaxue',cj,0)) huaxue 
from hzl 
group by name

2.列转行

(1)侧窗

hive中除了可以用常规的union all 外，还可以用侧窗

-- 侧窗写法
select name,km,cj 
from lzh2 lateral view explode(map('yuwen',yuwen,'shuxue',shuxue,'huaxue',huaxue)) tmp as km,cj;

(2)union

-- 常规写法
select name,'shuxue' km,shuxue cj from lzh2 
union all 
select name,'yuwen',yuwen from lzh2 
union all 
select name,'huaxue',huaxue from lzh2;