我想将下表中的给定记录显示为下表中所示的特定格式。
创建表：Test_1
CREATE TABLE Test_1
(
ColumnA varchar,
ColumnB varchar
);
记录插入：
INSERT INTO Test_1 values('A101','B101'),('A102','B102'),
('A103','B103'),('A104','B104'),
('A105','B105'),('A106','B106'),
('A107','B107'),('A108','B108'),
('A109','B109'),('A201','B201');
我想展示这样的结果：
预期结果：
ColumnA ColumnX
---------------------------------------------------------------------------------------------------------------------------------------
A101 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" = 1, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A102 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" = 0, "B102" = 1, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A103 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" = 0, "B102" = 0, "B103" = 1, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A104 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 1, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A105 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 1, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A106 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 1, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A107 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 1, "B108" = 0, "B109" = 0, "B201" = 0
A108 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 1, "B109" = 0, "B201" = 0
A109 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 1, "B201" = 0
A201 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 1
B101 "A101" = 1, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B102 "A101" = 0, "A102"= 1 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B103 "A101" = 0, "A102"= 0 , "A103"= 1, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B104 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 1, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B105 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 1, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B106 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 1, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B107 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 1 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B108 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 1, "A109"= 0, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B109 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 1, "A201"= 0, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B201 "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 1, "B101" = 0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
下面的脚本完成了一半的工作：这个脚本只在第一列显示
ColumnA，在第二列显示ColumnB。SELECT columnA, array_agg(ColumnB) AS ColumnX
FROM (
SELECT ColumnA, right(ColumnA, -1)::int AS sorta
, '"' || ColumnB || '" = ' || count(m.ColumnB) AS ColumnB
FROM (SELECT DISTINCT ColumnA FROM Test_1) b
CROSS JOIN (SELECT DISTINCT ColumnB FROM Test_1) a
LEFT JOIN Test_1 m USING (ColumnA, ColumnB)
GROUP BY ColumnA, ColumnB
ORDER BY sorta, right(ColumnB, -1)::int
) sub
GROUP BY 1, sorta
ORDER BY sorta;
最佳答案
优化得很好：
WITH t(a, b, ct) AS (
SELECT columna, columnb, count(*)::int
FROM test_1
GROUP BY 1, 2
)
, matrix AS (
SELECT a.*, b.*, COALESCE(t.ct, 0) AS ct
FROM (SELECT DISTINCT ON (a) a, right(a, -1)::int AS sorta FROM t) a
CROSS JOIN (SELECT DISTINCT ON (b) b, right(b, -1)::int AS sortb FROM t) b
LEFT JOIN t USING (a, b)
)
(
SELECT a AS ab, array_agg('"' || b || '" = ' || ct ORDER BY sortb) AS x
FROM matrix
GROUP BY 1, sorta
ORDER BY sorta
)
UNION ALL
(
SELECT b , array_agg('"' || a || '" = ' || ct ORDER BY sorta)
FROM matrix
GROUP BY 1, sortb
ORDER BY sortb
);
SQL Fiddle.
所有括号都是必需的。
Sum results of a few queries and then find top 5 in SQL
关于sql - PostgreSQL 9.3:将两列合而为一，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29293033/