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
