來自單個查詢的多個結果到不同的列

我有 2 個看起來像這樣的表：

表 A：

CREATE TEMP TABLE table_a (
 ID_number_X text,
 ID_number_Y int
);
INSERT INTO table_a VALUES
 ('HT59452', 123),
 ('HT59453', 123),
 ('HT59454', 123),
 ('HT59455', 789),
 ('HT59456', 101);

表 B：

CREATE TEMP TABLE table_b (
 Column_1 text,
 ID_number_Y int,
 ID_number_X text
);
INSERT INTO table_b VALUES
 ('foo', 123, null),
 ('bar', 789, null),
 ('baz', 101, null);

我正在執行以下查詢以將 ID_number_X 帶到 Table_B：

UPDATE table_b AS B
SET id_number_x = A.id_number_x
FROM table_a AS A
WHERE B.id_number_y = A.id_number_y

這很好用，但是，在某些情況下可能有多個結果，即 id_number_y 可以有多個 id_number_x 與之關聯。

如何將這些多個結果（當它們發生時）放入其他列？

所以我想要從上面的範例表中得到的結果是：

Table B
column_1    id_number_y     id_number_x1   id_number_x2   id_number_x3
foo            123            HT59452        HT59453        HT59454
bar            789            HT59455
baz            101            HT59456

我認為每個 id_number_y 最多可以有 5 個 id_number_x，所以我可以愉快地創建儲存這些所需的額外列。

一些東西table_b的架構必須是固定的。您不能有更新變數列的更新，其中一些甚至可能不會被創建。因為您不知道會有多少匹配項，所以您可能需要的是數組類型。

ALTER TABLE table_b
 DROP COLUMN ID_number_X,
 ADD COLUMN ID_number_X text[];

text[]現在您在桌子上有一個數組類型。

     Table "pg_temp_6.table_b"
  Column    |   Type    | Modifiers 
-------------+-----------+-----------
column_1    | text      | 
id_number_y | integer   | 
id_number_x | integer[] | 

您可以繼續創建UPDATE. 接下來，您需要一個簡單的查詢來進行更新。

SELECT id_number_y, array_agg(id_number_x) AS id_number_x
FROM table_a
GROUP BY id_number_y;

id_number_y |        id_number_x         
-------------+---------------------------
        123 | {HT59452,HT59453,HT59454}
        101 | {HT59456}
        789 | {HT59455}

有了這個，您可以將查詢包裝在 中UPDATE並獲得您想要的。

UPDATE table_b AS B
SET id_number_x = A.id_number_x
FROM (
   SELECT id_number_y, array_agg(id_number_x) AS id_number_x
   FROM table_a
   GROUP BY id_number_y
) AS A
WHERE B.id_number_y = A.id_number_y;

現在你有..

# TABLE TABLE_b;
column_1 | id_number_y |        id_number_x        
----------+-------------+---------------------------
foo      |         123 | {HT59452,HT59453,HT59454}
bar      |         789 | {HT59455}
baz      |         101 | {HT59456}

也就是說，這很少需要，還有其他兩種方法可以更好地滿足您的需求，

1. MATERIALIZED VIEW
2. VIEW

通常，這不是必需的，因為您只需使用JOIN.

引用自：https://dba.stackexchange.com/questions/163044