PostgreSQL交叉表查詢有沒有人知道如何在PostgreSQL中創(chuàng)建交叉表查詢？例如，我有下表：Section Status CountA Active 1A Inactive 2B Active 4B Inactive 5我想查詢返回以下交叉表：Section Active InactiveA 1 2B 4 5這可能嗎？
4 回答
慕容3067478
TA貢獻(xiàn)1773條經(jīng)驗(yàn) 獲得超3個贊
您可以使用附加模塊tablefunc的crosstab()功能- 您必須為每個數(shù)據(jù)庫安裝一次。從PostgreSQL 9.1開始，你可以使用它：CREATE EXTENSION
CREATE EXTENSION tablefunc;
在你的情況下，我相信它看起來像這樣：
CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);
INSERT INTO t VALUES ('A', 'Active', 1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active', 4);
INSERT INTO t VALUES ('B', 'Inactive', 5);
SELECT row_name AS Section,
category_1::integer AS Active,
category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2)
AS ct (row_name text, category_1 text, category_2 text);
qq_遁去的一_1
TA貢獻(xiàn)1725條經(jīng)驗(yàn) 獲得超8個贊
SELECT section,
SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly
SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status value as a separate column explicitly
FROM t
GROUP BY section
蝴蝶刀刀
TA貢獻(xiàn)1801條經(jīng)驗(yàn) 獲得超8個贊
使用JSON聚合的解決方案：
CREATE TEMP TABLE t (
section text
, status text
, ct integer -- don't use "count" as column name.
);
INSERT INTO t VALUES
('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
, ('C', 'Inactive', 7);
SELECT section,
(obj ->> 'Active')::int AS active,
(obj ->> 'Inactive')::int AS inactive
FROM (SELECT section, json_object_agg(status,ct) AS obj
FROM t
GROUP BY section
)X
添加回答
舉報(bào)
0/150
提交
取消