將結果轉換為數組
我有很多實體，例如：
使用者
id | name ----------- 1 | Joe 2 | David 3 | Jane汽車
id| name ------------ 1 | cars1 2 | cars2 3 | cars3 4 | cars4 5 | cars5 6 | cars6 7 | cars7 8 | cars8 9 | cars9汽車數據
id | price | category | uid | car_id ---+--------+----------+-----+------- 1 | 225.00 | p1 | 1 | 1 2 | 451.00 | p2 | 1 | 1 3 | 324.00 | p2 | 1 | 2 4 | 784.00 | p2 | 1 | 3 5 | 724.00 | p3 | 1 | 2 6 | 214.00 | p1 | 2 | 1 7 | 451.00 | p1 | 2 | 2 8 | 926.00 | p1 | 2 | 3 9 | 271.00 | p2 | 2 | 3 10 | 421.00 | p2 | 2 | 4 11 | 684.00 | p2 | 2 | 2 12 | 124.00 | p3 | 2 | 5 13 | 128.00 | p3 | 2 | 1 14 | 741.00 | p1 | 3 | 1 15 | 965.00 | p1 | 3 | 3 16 | 124.00 | p2 | 3 | 4 17 | 415.00 | p2 | 3 | 1 18 | 51.00 | p2 | 3 | 2 19 | 965.00 | p2 | 3 | 6過濾器
id | name | filter | uid ----+ --------+------------------+----- 1 | filter1 | string filters 1 | 1 2 | filter2 | string filters 2 | 1 3 | filter3 | string filters 3 | 1 4 | filter3 | string filters 3 | 1 5 | filter3 | string filters 3 | 1 6 | filter3 | string filters 3 | 1 7 | filter3 | string filters 3 | 1 8 | filter | string filters 1 | 2 9 | filter5 | string filters 5 | 2 10 | filter6 | string filters 6 | 2 11 | filter6 | string filters 6 | 2 12 | filter6 | string filters 6 | 2 13 | filter6 | string filters 6 | 2 14 | filter7 | string filters 7 | 3 15 | filter8 | string filters 8 | 3 16 | filter8 | string filters 8 | 3 17 | filter8 | string filters 8 | 3 18 | filter8 | string filters 8 | 3 19 | filter9 | string filters 9 | 3分配過濾器
uid | category | filter_id ----+ ----------+----------- 1 | p1 |1 1 | p2 |1 1 | p2 |2 1 | p2 |3 1 | p3 |4 2 | p1 |9 2 | p1 |8 2 | p1 |13 3 | p2 |14 3 | p2 |16 3 | p2 |17 3 | p3 |19 3 | p3 |18 3 | p1 |14 3 | p1 |18我想要的是這樣的結果：
uid | category | filter_id |car_id ----+ ----------+-----------+-------- 1 | p1 |1 |[1] 1 | p2 |1 |[1,2,3] 1 | p2 |2 |[1,2,3] 1 | p2 |3 |[1,2,3] 1 | p3 |4 |[2] 2 | p1 |9 |[1,2,3] 2 | p1 |8 |[1,2,3] 2 | p1 |13 |[1,2,3] 3 | p2 |14 |[1,2,3,4,6] 3 | p2 |16 |[1,2,3,4,6] 3 | p2 |17 |[1,2,3,4,6] 3 | p3 |19 |[6,7] 3 | p3 |18 |[6,7] 3 | p1 |14 |[1] 3 | p1 |18 |[1]如何更改原始 SQL 查詢以提供上述結果？
更新：
- 每個使用者都可以為自己定義一個過濾器，每個過濾器都特定於一個使用者。
- 每個使用者都可以將汽車放在 cars_data
表中的特定類別中。（類別是
$$ p1,p2,p3 $$)
- 每個使用者都可以為他們的每個類別分配多個過濾器。
我需要知道每個使用者使用了哪些過濾器，以及過濾器應用於哪些汽車。
例如，使用者 1 已將 cars1、cars2、cars3 放在類別 p1 中。此外，已將 filter1、filter2、filter3 分配給此類別。我需要的結果是這樣的：
uid | category | filter_id |car_id ----+ ----------+-----------+-------- 1 | p2 |1 |[1,2,3] 1 | p2 |2 |[1,2,3] 1 | p2 |3 |[1,2,3]
SELECT cd.uid, cd.category, f.id AS filter_id, f.name, f.filter, array_agg(cd.car_id) AS car_ids FROM cars_data cd JOIN assign_filters af USING (uid, category) JOIN filters f ON f.id = af.filter_id GROUP BY 1,2,3 ORDER BY 1,2,3;db<>在這裡擺弄
我需要從表中獲取
name和filter欄位filters（如果過濾器名稱值不唯一）我使用
f.id AS filter_id而不是af.filter_id. 與表格一樣，f.id它涵蓋PRIMARY KEY了filters表格中的所有列GROUP BY，並且我們不需要添加filters您想要包含在SELECT列表中的任何其他列。
GROUP BY關於和中的序數ORDER BY
SELECT上述查詢參考列表項中的序號：GROUP BY 1,2,3 ORDER BY 1,2,3相當於：
GROUP BY cd.uid, cd.category, f.id ORDER BY cd.uid, cd.category, f.id詳細的形式通常是首選，有些人非常強調。但這並非沒有警告。SQL 標準有特殊的規則如何以不同的方式解析
ORDER BY標識符GROUP BY。輸出列的名稱可用於在
ORDER BYandGROUP BY子句中引用列的值，但不能在WHEREorHAVING子句中；在那裡你必須寫出表達式。如果
ORDER BY表達式是同時匹配輸出列名和輸入列名的簡單名稱，ORDER BY則將其解釋為輸出列名。GROUP BY這與在相同情況下做出的選擇相反 。這種不一致性是為了與 SQL 標準兼容。在這種特殊情況下，我們使用
f.id而不是af.filter_id，但我們希望“filter_id”作為別名（輸出列名）與後者衝突。另外，有uid. 其中兩個合併在該USING子句中。有很多方法可以解決這個問題。安全的方法是在任何地方進行表限定，如圖所示。具有衝突別名的表達式（此處沒有）必須重複拼寫，無論多麼冗長。
ORDER BY和GROUP BY(and ) 中的序數DISTINCT ON是一個簡單、明確的替代方案。當然，對主要SELECT列表項的更改會影響參考，這可能會受到歡迎，也可能不受歡迎。在任何情況下都需要觀察它，這就是潛在的缺點。