加入两个表并将多个关联合并成一行
问题描述:
我目前在加入两个PSQL表时被卡住了。我知道这些问题总是被问到,但我却无法找到适合我情况的答案。我为模糊的问题标题道歉。加入两个表并将多个关联合并成一行
描述简化情况:我们有两个表格,children
和parents
。正如你所看到的,孩子们用逗号分隔的“阵列”与他们的父母连接起来。我想创建一个简单的视图(非物化),从孩子的角度来看,父母与孩子一起参加。
表儿童
| id | name | parents |
| -- | ----- | ------- |
| 1 | Bob | 1,2 |
| 2 | Alice | 3 |
表父母
| id | name | phone |
| -- | ----- | ----- |
| 1 | Carol | 1234 |
| 2 | Frank | 5678 |
| 3 | Grace | 9012 |
所需组合视图
| child_id | child_name | parent1_name | parent1_phone | parent2_name | parent2_phone |
| -------- | ---------- | ------------ | ------------- | ------------ | ------------- |
| 1 | Bob | Carol | 1234 | Frank | 5678 |
| 2 | Alice | Grace | 9012 | | |
我试图达到上述具有以下视图定义:
SELECT children.id AS child_id,
children.name AS child_name,
parents.name AS parent_name,
parents.phone AS parent_phone
FROM children
JOIN parents ON parents.id::text =
ANY (string_to_array(children.parents, ','::text));
当然,这并不孩子的父母双方合并成一排:
| child_id | child_name | parent_name | parent_phone |
| 1 | Bob | Carol | 1234 |
| 1 | Bob | Frank | 5678 |
| 2 | Alice | Grace | 9012 |
什么是我想要的结果的最佳解决方案?
答
您需要根据您当前的查询后,创造出一些聚集
WITH cte as (
SELECT *, row_number() over (partition by child_id order by "parent_name") as rn
FROM queryResult
)
SELECT child_id,
child_name,
MAX(CASE WHEN rn = 1 then parent_name END) as parent_name_1,
MAX(CASE WHEN rn = 1 then parent_phone END) as parent_phone_1,
MAX(CASE WHEN rn = 2 then parent_name END) as parent_name_2,
MAX(CASE WHEN rn = 2 then parent_phone END) as parent_phone_2
FROM cte
GROUP BY child_id, child_name
ORDER BY child_id
输出
注:这个假设最多只有一个孩子只有2个父母。所以,如果你过分简化了你的问题或者在你的数据中拥有现代家庭,那么要小心。
谢谢你的答案。不幸的是,我的数据库由许多现代家庭组成;)我简化了我的问题。实际上,我的'孩子'可以有多达5个父母(但也是零)。来自“父母”表的行最多包含5个要在视图中合并的列。这会使你为我提供的查询非常麻烦。在处理更大的家庭和更多父数据列时,是否有办法缩短或简化查询? –
是的,我想这可能是一个问题。但如果是'高达5'我仍然认为可以工作。但你也有交叉表函数(你需要添加一个扩展)检查这个[**教程**](http://www.vertabelo.com/blog/technical-articles/creating-pivot-tables-in-postgresql-使用最交叉函数)。 –