面试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函数聚合

那个公司呀