本文介绍了PostgreSQL 9.3:将两列合而为一的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将下表中的给定记录显示为下表中所示的特定格式。
I want to show the given records in the following table into the specific format which is shown below in the table.
创建表:Test_1
Creating table: 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以下脚本可以完成一半工作:该脚本仅在第一列和 ColumnB中显示 ColumnA code>在第二列中。
Here is the following script does the half work: This script only display the ColumnA in a first Column and ColumnB in the second column.
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小提琴。
需要所有括号。
- 求和一些查询的结果,然后在SQL中找到前5名
- Sum results of a few queries and then find top 5 in SQL
更多推荐
PostgreSQL 9.3:将两列合而为一
发布评论