【sql】行专列，列转行汇总

生活明朗，万物可爱❤️

人间值得，未来可期❤️

大家好

首先感谢小伙伴们能够关注我并转发文章

我原本打算是能够将知识点

以讲段子的方式讲出来

在此我也会继续努力，加油共勉

-- 列转行
-- 这里的max是为了将无数据的值设为0，避免出现null
SELECT username ,
    MAX(CASE subject WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE subject WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE subject WHEN '英语' THEN score ELSE 0 END ) 英语,
    MAX(CASE subject WHEN '生物' THEN score ELSE 0 END ) 生物
FROM stu_score
GROUP BY username;

select username, '数学' subject , math as score from test.stu_score2
UNION ALL
select username, '语文' subject, chinese as score from test.stu_score2
UNION ALL
select username, '英语' subject, english as score from test.stu_score2
UNION ALL
select username, '生物' subject, biological as score from test.stu_score2
order by username,subject;

select 
  user_id,
  concat_ws(',',collect_list(order_id)) as order_value
from col_lie
group by user_id;

select 
  user_id,
  order_value,
  order_id
from lie_col
lateral view explode(split(order_value,',')) num as order_id