postgresql - 使用动态目标列进行查询
问题描述
我正在尝试在 Postgres 中构建一个使用 3 个数据表的视图。我不确定这是否可能,并且在谷歌上搜索了一下,但没有找到任何结论。这就是我正在尝试做的事情:
我有一个项目名称表 - 假设有 5 个项目:
fruits
id | name
1 | banana
2 | orange
3 | pear
4 | apple
5 | grape
然后我有一个人的名单
people
id | name
1 | Joe Blow
2 | Sally Smith
3 | John Jones
4 | Sam Benny
5 | Nick Stevens
6 | Peter Sandwitch
7 | Sarah Morgan
然后我有第三个表链接上述两个:
people_fruits
person_id | fruit_id
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 3
3 | 5
6 | 3
7 | 3
7 | 4
我想要做的是能够利用上述内容动态创建一个视图,该视图将根据水果表的内容更改列。例如,我希望视图显示上述数据如下:
my_fruity_view
name | bananna | orange | pear | apple | grape
Joe Blow | X | X | X | X |
Sally Smith | X | | X | |
John Jones | | | | | X
Sam Benny | | | | |
Nick Stevens | | | | |
Peter Sandwitch | | | X | |
Sarah Morgan | | | X | X |
然后,如果我稍后添加水果芒果,下次运行查询(不修改)时,它会将其添加为一列:
my_fruity_view
name | bananna | orange | pear | apple | grape | mango
Joe Blow | X | X | X | X | |
Sally Smith | X | | X | | |
John Jones | | | | | X |
Sam Benny | | | | | |
Nick Stevens | | | | | |
Peter Sandwitch | | | X | | |
Sarah Morgan | | | X | X | |
这样的查询可能吗?我在堆栈溢出时看到了一些这样的事情——但它似乎是在每列的基础上完成的——但我的数据需要是动态的。
我可以通过编程来实现这一点,但我更愿意将它打包成一个视图以保持整洁。对此的任何帮助将不胜感激。
解决方案
基本上你想要一个数据透视表或交叉表。附加模块tablefunc
提供了您需要的功能。如果您不熟悉它,请先阅读以下内容:
您的案例的特殊困难:您首先需要一个查询连接表以产生正确的输入:
SELECT p.name, f.name, text 'x' AS marker -- required, logically redundant column
FROM people p
LEFT JOIN people_fruits pf ON pf.person_id = p.id -- LEFT JOIN !
LEFT JOIN fruits f ON f.id = pf.fruit_id
ORDER BY p.id, f.id; -- seems to be the desired sort order
LEFT [OUTER] JOIN
,所以你不会失去没有果实的人。
在带有两个参数的crosstab()
函数中使用它,如下所示:
SELECT * FROM crosstab(
$$SELECT p.name, f.name, text 'x'
FROM people p
LEFT JOIN people_fruits pf ON pf.person_id = p.id
LEFT JOIN fruits f ON f.id = pf.fruit_id
ORDER BY p.id$$
,$$VALUES ('bananna'), ('orange'), ('pear'), ('apple'), ('grape')$$)
AS ct (name text, bananna text, orange text, pear text, apple text, grape text);
目标列列表中的水果顺序必须与第二个参数中的水果顺序相匹配(id
在您的情况下按顺序排列)。
缺少的水果得到一个NULL
价值。
但是,这还不是动态的。完全动态的 SQL 严格来说是不可能的,这需要在调用时知道结果列。一种或另一种方式,您需要两次往返数据库服务器。您可以让 Postgres 动态构建交叉表查询,然后在下一步中执行它。
代码示例的相关答案:
另一种方法是返回包含动态元素列表的数组或文档类型 ( json
, xml
, ...) 。
推荐阅读
- android - GestureDetector() 包装文本小部件时没有“onTap”属性
- javascript - 可以在 npm run dev - ubuntu 中找到此运行的完整日志
- python - 从 Matplotlib 图形问题中反转轴
- python - 如何从不断变化并连接到数据库的网站获取值?
- javascript - react/redux 状态和组件 useState 在快速点击时变得不同步
- oracle - Oracle sql中表值函数的循环
- parallel-processing - 读取变量会成为 OpenMP 中的数据竞争吗?
- c# - 从现有 csproj 获取默认命名空间(Microsoft.NET.Sdk 格式)
- java - JavaFX 菜单项名称
- python - Django Football Simulation - 循环浏览视图时获取“ERR_TOO_MANY_REDIRECTS”浏览器消息