面试sql题

SELECT stu_id ,CONCAT( MAX(CASE WHEN rnk = 1 THEN score END),'|' ,MAX(CASE WHEN rnk = 2 THEN score END),'|' ,MAX(CASE WHEN rnk = 3 THEN score END) ) AS top_grade FROM ( SELECT * ,row_number() over(PARTITION BY gt.stu_id ORDER BY score DESC) AS rnk FROM ( SELECT stu_id, '语文' AS SUBJECT, cn_grade AS score FROM grade UNION ALL SELECT stu_id, '数学' AS SUBJECT, mat_grade AS score FROM grade UNION ALL SELECT stu_id, '英语' AS SUBJECT, en_grade AS score FROM grade UNION ALL SELECT stu_id, '物理' AS SUBJECT, phcs_grade AS score FROM grade UNION ALL SELECT stu_id ,'化学' AS SUBJECT, chmc_grade AS score FROM grade ) gt ORDER BY stu_id ) a GROUP BY stu_id

先用窗口函数求出各个学生前三高的成绩 然后用group_concat函数聚合

平常写 hive，不确定 mysql 是不是可以这么写。 一行转一列“行转列”已经有大佬写了。这种方法好在如果要求 rank/dense_rank 并列也能支持。 如果不想写“行转列”，还可以把六列拼成 array 再 sort_array，升序的话取最后3个。 这种方法缺点就是不支持 rank/dense_rank 的并列情况了，就像 row_number 排序

那个公司呀