Postgresql 行转列两种简单方法

数据准备

CREATE TABLE tb_student_score
(
  name   varchar,
  course varchar,
  score  int
);
INSERT INTO tb_student_score
VALUES ('张三', '数学', 83);
INSERT INTO tb_student_score
VALUES ('张三', '英语', 93);
INSERT INTO tb_student_score
VALUES ('张三', '语文', 80);
INSERT INTO tb_student_score
VALUES ('李四', '语文', 74);
INSERT INTO tb_student_score
VALUES ('李四', '数学', 84);
INSERT INTO tb_student_score
VALUES ('李四', '英语', 94);

准备后的数据

期望结果

列转行查询方法

1. 连接查询

select ch.name 姓名, ch.china_score 语文, math.math_score 数学, en.english_score 英语
from (select name, score china_score from tb_student_score where course = '语文') ch
       left join
       (select name, score math_score from tb_student_score where course = '数学') math on ch.name = math.name
       left join
     (select name, score english_score from tb_student_score where course = '英语') en on ch.name = en.name;

2. 分组查询

select name 姓名,
       sum(case when course='语文' then score else 0 end) 语文,
       sum(case when course='数学' then score else 0 end) 数学,
       sum(case when course='英语' then score else 0 end) 英语
from tb_student_score group by name;