sql（数据分析群）

1.长表变宽表，使用case-when即可

select username,
       max(case when sub = 'a' then per else null end) '英语',
	   max(case when sub = 'b' then per else null end) '数学',
	   max(case when sub = 'd' then per else null end) '化学',
	   max(case when sub = 'e' then per else null end) '历史'
from score
where (sub in ('a','b') and per > 60) or (sub in ('d','e') and per > 80)
group by username
having 英语 <> 'null' and 数学 <> 'null' and 化学 <> 'null' and 历史 <> 'null'
order by username;

2.

select username,GROUP_CONCAT(sub) g_sub
from(
		select *,row_number() over(partition by username order by per desc) r
		from score) t
where r <=3
group by username;

3.

select username,sub
from(
		select *,row_number() over(partition by username order by per desc) r
		from score) t
where r <=3

第三题投机取巧，按照题目意思应该是要用 hive 的 lateral view explode(split(_,’,’)) 解决，
hive刚开始学习，等学完来补充