PostgreSQL 9.3:交叉表查询
问题描述:
我有一个表称为测试,它具有四列,如下所示。PostgreSQL 9.3:交叉表查询
create table testing
(
cola varchar(10),
colb varchar(10),
colc varchar(10),
cold varchar(10)
);
插入一些行:
insert into testing values('A1','City1','X1','Z1'),
('A2','City2','X2','Z2'),
('A3','City3','X3','Z3'),
('A4','City4','X4','Z4');
预期结果:我想表明它看起来像如下所示的结果:
cola City1 City2 City3 City4 colc cold
--------------------------------------------
A1 1 0 0 0 X1 Z1
A2 0 1 0 0 X2 Z2
A3 0 0 1 0 X3 Z3
A4 0 0 0 1 X4 Z4
我尝试:
我试着用下面的脚本,但得到一个错误。
select * from crosstab
(
'select cola,colb,count(colb),colc,cold from testing group by 1,2,4,5 order by 1,2,4,5',
'select distinct colb from testing'
)
as t
(
cola varchar(10),City1 integer,City2 integer,City3 integer,City4 integer,colc varchar(10),cold varchar(10)
);
错误详细信息:
ERROR: invalid input syntax for integer: "City1"
答
也许这是你想要的吗?
select x1.*, x2.colc, x2.cold from crosstab
($$select x1.cola, x2.colb,
CASE WHEN x1.colb = x2.colb THEN 1 ELSE 0 END
FROM testing x1 CROSS JOIN testing x2
$$
) AS x1(cola VARCHAR(10), City1 INT, City2 INT, City3 INT, City4 INT)
LEFT JOIN testing x2 ON x1.cola = x2.cola;
结果:
cola | city1 | city2 | city3 | city4 | colc | cold
------+-------+-------+-------+-------+------+------
A1 | 1 | 0 | 0 | 0 | X1 | Z1
A2 | 0 | 1 | 0 | 0 | X2 | Z2
A3 | 0 | 0 | 1 | 0 | X3 | Z3
A4 | 0 | 0 | 0 | 1 | X4 | Z4
什么'A1'和'City1'之间的关系? – Kokizzu 2015-02-11 07:37:50
@Kokizzu,这只是一个例子。我可以说'A1'是产品代码,'City1'是已售完的城市。 – MAK 2015-02-11 07:40:54