PolarDB PostgreSQL版(兼容Oracle)支持PIVOT语法,您可以将任何查询以交叉表格形式显示。与一般查询相比,交叉表格形式具有更多的列和较少的行。
语法
SELECT ....
FROM <table-expr>
PIVOT
(
aggregate-function(<column>) AS <alias>
FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
) AS <alias>
WHERE .....
示例
创建示例数据库。
CREATE TABLE SalesOrder
(
shop VARCHAR(50),
product VARCHAR(50),
sales INT
);
insert into SalesOrder values('shopA', 'A', 10);
insert into SalesOrder values('shopA', 'B', 23);
insert into SalesOrder values('shopA', 'B', 63);
insert into SalesOrder values('shopA', 'B', 75);
insert into SalesOrder values('shopA', 'C', 39);
insert into SalesOrder values('shopB', 'A', 25);
insert into SalesOrder values('shopB', 'A', 43);
insert into SalesOrder values('shopB', 'C', 86);
insert into SalesOrder values('shopB', 'C', 33);
insert into SalesOrder values('shopC', 'B', 93);
insert into SalesOrder values('shopC', 'B', 92);
insert into SalesOrder values('shopC', 'B', 27);
insert into SalesOrder values('shopC', 'B', 6);
insert into SalesOrder values('shopC', 'C', 79);
普通形式显示的查询:
select * from SalesOrder pivot(sum(sales) for product in ('A', 'B'));
查询结果如下:
shop | 'A' | 'B' -------+-----+----- shopC | | 218 shopB | 68 | shopA | 10 | 161 (3 rows)
交叉表格形式显示的查询:
select * from (select shop, product, sales from SalesOrder where sales>10) pivot(sum(sales) for product in ('A', 'B'));
查询结果如下:
shop | 'A' | 'B' -------+-----+----- shopC | | 212 shopB | 68 | shopA | | 161 (3 rows)
文档内容是否对您有帮助?