hive行转列

最新推荐文章于 2025-05-19 19:09:54 发布

流觞、

最新推荐文章于 2025-05-19 19:09:54 发布

阅读量280

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： sql hive

本文链接：https://blog.youkuaiyun.com/jdk1994/article/details/114373303

数据库专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一种使用SQL进行数据整理的方法，通过将学生姓名与各科成绩映射为键值对，实现数据的横向和纵向整合。此外还展示了如何利用CASE WHEN语句获取指定类别下的最高分。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--转成map，再取值

WITH student AS
(
SELECT '张三' AS name ,'语文' AS class, 100 AS score FROM system.dual UNION ALL
SELECT '张三' AS name ,'数学' AS class, 88 AS score FROM system.dual UNION ALL
SELECT '李四' AS name ,'语文' AS class, 98 AS score FROM system.dual UNION ALL
SELECT '李四' AS name ,'数学' AS class, 99 AS score FROM system.dual
)
WITH tb AS
(
SELECT
name,
str_to_map(CONCAT_WS(',',collect_list(concat(class,':',score))),',',':') as kv
FROM STUDENT GROUP BY name
)
SELECT name AS name,map_values(kv)[0] AS '语文',map_values(kv)[1] AS '数学' FROM tb;

--CASE WHEN 方式

WITH student AS
(
SELECT '张三' AS name ,'语文' AS class, 100 AS score FROM system.dual UNION ALL
SELECT '张三' AS name ,'数学' AS class, 88 AS score FROM system.dual UNION ALL
SELECT '李四' AS name ,'语文' AS class, 98 AS score FROM system.dual UNION ALL
SELECT '李四' AS name ,'语文' AS class, 99 AS score FROM system.dual
)
SELECT name AS name ,
max(CASE WHEN class='语文' THEN score ELSE NULL END ) AS '语文',
max(CASE WHEN class='数学' THEN score ELSE NULL END ) AS '数学'
FROM student
GROUP BY name;