postgresql中如何把列中的每一行数据变成列的标题

2024/10/24 4:37:59 来源：https://blog.csdn.net/Java_Fly1/article/details/140116053 浏览: 次关键词：postgresql中如何把列中的每一行数据变成列的标题

postgresql中如何把列中的每一行数据变成列的标题

测试表
方法一
- 安装 tablefunc 扩展
- 使用 crosstab 函数
- - 注意事项
- 测试结果
方法二
- 优点
- 缺点
- 测试结果

测试表

CREATE TABLE public.benefits (id int4 NOT NULL,workplace varchar(255) NULL,province varchar(255) NULL,benefits int4 NULL,CONSTRAINT offer_benefits_pkey PRIMARY KEY (id)
);

INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(1, '合肥', '合肥', 0);
INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(2, '合肥', '上海', 1000);
INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(3, '合肥', '北京', 1000);
INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(4, '合肥', '西安', 1000);
INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(5, '上海', '上海', 0);
INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(6, '上海', '合肥', 1000);
INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(7, '上海', '北京', 1000);
INSERT INTO public.benefits
(id, workplace, province, benefits)
VALUES(8, '上海', '西安', 1000);

方法一

在 PostgreSQL 中，如果你想将表中的某一列的数据变成列标题（通常被称为“列转行”或“交叉表”操作）
你可以使用 crosstab 函数，这个函数是 PostgreSQL 提供的 tablefunc 扩展中的一部分

安装 tablefunc 扩展

CREATE EXTENSION IF NOT EXISTS tablefunc;

安装成功，可以在扩展中查看
在这里插入图片描述

使用 crosstab 函数

SELECT *
FROM crosstab($$SELECT workplace, province, benefits FROM postgres.public.benefits  ORDER BY workplace, province$$
) AS ct (workplace varchar, "合肥" int, "上海" int, "北京" int, "西安" int);

在这里，crosstab 函数的第一个参数是一个 SQL 查询，返回的结果必须包含三列：行标题列（workplace）、列标题列（province）和数值列（benefits ）。第二个参数是结果表的列定义，必须明确指定每个新列的名称和数据类型。

注意事项

列标题必须事先知道：在定义 crosstab 函数的输出列时，必须提前知道所有可能的列标题（即 province列的所有可能值）。
数据类型匹配：定义的列数据类型必须与原始表中的数据类型匹配。
数据量大的情况：如果有很多列（即 province的不同值很多），这个过程可能变得复杂，可以考虑动态生成列名和数据类型。

测试结果

在这里插入图片描述

方法二

除了使用 crosstab 函数，PostgreSQL 还可以使用条件聚合（conditional aggregation）来实现相同的效果。这种方法不需要安装 tablefunc 扩展，直接通过 SQL 语句中的聚合函数和 CASE 语句来实现。

SELECTworkplace,MAX(CASE WHEN province = '合肥' THEN benefits ELSE 0 END) AS "合肥",MAX(CASE WHEN province = '上海' THEN benefits ELSE 0 END) AS "上海",MAX(CASE WHEN province = '北京' THEN benefits ELSE 0 END) AS "北京",MAX(CASE WHEN province = '西安' THEN benefits ELSE 0 END) AS "西安"
FROMpostgres.public.benefits  
GROUP BYworkplace;

MAX(CASE WHEN province = ‘合肥’ THEN benefits ELSE 0 END): 这个表达式会在 province 为合肥时返回 benefits 的值，否则返回 0。然后使用 MAX 函数来获取该组中最大的值（实际上这里只有一个非零值）
GROUP BY workplace: 按照 workplace 列进行分组，确保每个 workplace 只有一行结果。