PostgreSql数据库行转列的方法

PostgreSql数据库行转列的方法

大家好我是星仔 在这里介绍下PostgreSql数据库sql实现行专列的方法

准备数据

CREATE TABLE test.score (
	id varchar(255) NULL,
	subject varchar(32) NULL,
	score int4 NULL
);
INSERT INTO test.score (id, subject, score) VALUES('1', 'java', 99);
INSERT INTO test.score (id, subject, score) VALUES('1', 'go', 78);
INSERT INTO test.score (id, subject, score) VALUES('1', 'cpp', 59);
INSERT INTO test.score (id, subject, score) VALUES('2', 'java', 100);
INSERT INTO test.score (id, subject, score) VALUES('2', 'go', 72);
INSERT INTO test.score (id, subject, score) VALUES('2', 'cpp', 68);

1. 使用group by (效率高 ，只查询了一次表)

   SELECT
       id,
       MAX(CASE WHEN subject = 'java' THEN score END) AS java,
       MAX(CASE WHEN subject = 'go' THEN score END) AS go,
       MAX(CASE WHEN subject = 'cpp' THEN score END) AS cpp
   FROM
       test.score
   GROUP BY
       id
   ORDER BY
       id;
   

   解释:

   根据唯一编号分组，使用 CASE WHEN 表达式来检查 subject 列的值，并根据条件返回相应的 score 需要注意的是max函数只是保证获取唯一值的手段 也可用其他聚合函数

2. 转换成jsonb（效率高，查询一次表，要转化的列多时更佳）

   SELECT
       id,
       (scores->>'java')::int AS java,
       (scores->>'go')::int AS go,
       (scores->>'cpp')::int AS cpp
   FROM (
       SELECT
           id,
           jsonb_object_agg(subject, score) AS scores
       FROM
           test.score
       GROUP BY
           id
   ) AS subquery
   ORDER BY
       id;
   

   解释:

   先使用jsonb_object_agg函数将要转换的属性转为jsonb 再获取jsonb中对应的key将其转为int类型（对json的操作不明白可以看看我的另一篇关于pg数据库json函数运用

pg数据库json常用函数

）

3. 使用join拼接（效率低 ，不推荐）

   SELECT
   	t1.ID,
   	t1.java,
   	t2.GO,
   	t3.cpp 
   FROM
   	( SELECT ID, score AS java FROM test.score WHERE subject = 'java' ) AS t1
   	JOIN ( SELECT ID, score AS GO FROM test.score WHERE subject = 'go' ) AS t2 ON t1.ID = t2.
   	ID JOIN ( SELECT ID, score AS cpp FROM test.score WHERE subject = 'cpp' ) AS t3 ON t1.ID = t3.ID
   

   解释:

   每次查询表 找出需要的字段的值join拼接起来。 效率低，多次查询全表。不推荐使用。