--求学生们每门课成绩,展示格式
--1 80-90-xx-xx-xx
--2 xx-xx-xx-xx-xx
--COLLECT_SET/COLLECT_LIST:把聚合后的列值放到集合中。
--CONCAT_WS:取出字符串集合中的每个元素,使用指定分隔符分隔
--CAST:hive中转换字符串类型的
SELECT student_id
,CONCAT_WS('-',COLLECT_LIST(CAST(score AS STRING)) )
FROM score
GROUP BY student_id;
--1 80 90 xx xx
--2 80 90 xx xx
--CASE...WHEN...THEN...END
SELECT student_id
,SUM(CASE WHEN course_name = 'yuwen'THEN score ELSE 0 END) `语文`
,SUM(CASE WHEN course_name = 'shuxue'THEN score ELSE 0 END) `数学`
FROM score
GROUP BY student_id;
SELECT student_id
,SUM(CASE
WHEN course_name != 'yuwen' --如果列名不是yuwen
THEN 0 --加0
ELSE score --否则加score
END) `语文`
,SUM(CASE
WHEN