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 allselect '张三' as name , '英语' as subject , 82 as score  union allselect '张三' as name , '语文' as subject , 95 as score  union allselect '李四' as name , '数学' as subject , 90 as score  union allselect '李四' as name , '英语' as subject , 93 as score  union allselect '李四