数据人员Sql必会——行转列

今天被问到列转行的问题，竟然没有回答上来，回想自己也是数据开发人员，平时的积累真是不到位，下面总结一下列转行。

假设咱们有一个学生得分数据表：student_score

CREATE TABLE student_scores
(
  user_name character varying(30) NOT NULL, -- 学生名称
  subject character varying(30) NOT NULL, -- 课程名称
  score integer, -- 得分
  CONSTRAINT student_scores_pkey PRIMARY KEY (user_name, subject)
);
COMMENT ON COLUMN student_scores.user_name IS '学生名称';
COMMENT ON COLUMN student_scores.subject IS '课程名称';
COMMENT ON COLUMN student_scores.score IS '得分';

是postgresql的语法，就四个字段，下面是数据库内容：

那么我希望把这些数据用另外的一种方式展现，比如：

那么如何使用sql实现哪？下面我贴出来代码：

第一种办法，逻辑简单 左关联：

SELECT 
  a.user_name,
  b.score as "语文",
  c.score as "数学",
  d.score as "英语",
  e.score as "生物"
FROM (select distinct user_name from student_scores) a
left join (select score, user_name FROM student_scores where subject = '语文') b on b.user_name=a.user_name
left join (select score, user_name FROM student_scores where subject = '数学') c on c.user_name=a.user_name
left join (select score, user_name FROM student_scores where subject = '英语') d on d.user_name=a.user_name
left join (select score, user_name FROM student_scores where subject = '生物') e on e.user_name=a.user_name

第二种方法，必然好了一些：

SELECT 
      user_name, 
      MAX(CASE subject WHEN '语文' THEN Score ELSE 0 END) AS "语文",
      MAX(CASE subject WHEN '数学' THEN Score ELSE 0 END) AS "数学",
      MAX(CASE subject WHEN '英语' THEN Score ELSE 0 END) AS "英语",
      MAX(CASE subject WHEN '生物' THEN Score ELSE 0 END) AS "生物"
FROM student_scores
GROUP BY user_name

 

转载于:https://www.cnblogs.com/liqiu/p/4925515.html