数据人员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必会——行转列

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

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

那么如何使用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

秒客网

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

相关文章