sql—如何对postgresql的嵌套数据执行联接操作

7y4bm7vi 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(273)

我有一堆表，我想对它们执行连接操作。他们看起来像这样
主要数据

+----+-------+--------------+
| id | title | mainCategory |
+----+-------+--------------+
| 1  | xyz   | 1            |
+----+-------+--------------+
| 2  | zba   | 2            |
+----+-------+--------------+
| 3  | ync   | 3            |
+----+-------+--------------+

子类别

+----+-------+--------------+
| ID | rowId | categoryID |
+----+-------+--------------+
| 7  | 1   | 1            |
+----+-------+--------------+
| 9  | 1   | 2            |
+----+-------+--------------+
| 10  | ync   | 3            |
+----+-------+--------------+
rowID is a foreign key that links a sub category to the main data

分类表

+----+-------+
| id | title |
+----+-------+
| 1  | apples   |             
+----+-------+
| 2  | Bananas   |
+----+-------+
| 3  | Tomatoes   |
+----+-------+

理想情况下，如果可能的话，我会像这样很好地整理数据。我只需要有subcategoryid和category值。

+----+-------+--------------+ --------------+
| id | title | mainCategory | SubCategory
+----+-------+--------------+ --------------+
| 1  | xyz   | 1            | (7 , Apples ), (9 , Bananas) |
+----+-------+--------------+--------------+
| 2  | zba   | 2            | Null
+----+-------+--------------+--------------+
| 3  | ync   | 3            | (10, Tomatoes )
+----+-------+--------------+--------------+

太好了！非常感谢。

sql postgresql Join Database String

来源：https://stackoverflow.com/questions/62862991/how-to-do-join-operation-on-nested-data-for-postgresql

1条答案

按热度按时间

tzcvj98z1#

一种选择是连接和字符串聚合。在postgres中，横向连接非常方便：

select m.*, x.*
from main m
cross join lateral (
    select string_agg('(' || sc.id || ', ' || c.title || ')', ', ') subcategory
    from sub_category sc
    inner join category c on c.id = sc.category_id
    where sc.rowId = m.main_category
) x

赞(0）回复(0）举报 2021-07-26

我来回答

sql—如何对postgresql的嵌套数据执行联接操作

1条答案

相关问题

热门标签

最新问答