SQL进阶技巧：多行转列问题中如何保证不同字段内容有序性及一一对应？【collect_list函数有序性保证问题】

目录

0 问题描述【小红书面试题】

1 数据准备

2 问题分析

3 小结

---

0 问题描述【小红书】

有如下需求，需要将左边的表变换成右边的表，注意字段内容的顺序及对应内容的一致性。

第一个字段为name,第二个字段为subject，第三个字段为score，变换后要求subject按照语文、数学、英语排列，且score和subject之间内容保持一一对应。

1 数据准备

with data as (
    select '张三' as name , '数学' as subject , 80 as score  union all
    select '张三' as name , '英语' as subject , 82 as score  union all
    select '张三' as name , '语文' as subject , 95 as score  union all
    select '李四' as name , '数学' as subject , 90 as score  union all
    select '李四' as name , '英语' as subject , 93 as score  union all
    select '李四