sql - 将多行合并为一列中具有多个行值的行
问题描述
我目前正在构建一个查询以从我的数据库中检索一些数据,我需要在一行中访问一些具有公共 id 的信息。
使用此查询:
select
missions_answer.response_id as "response",
crm_player."document" as "document",
missions_question.label as "label",
missions_answertext.body as "bill #",
missions_answerselectmultiple.body as "product",
missions_answerinteger.body as "answer"
from missions_answer
left join missions_question on missions_answer.question_id = missions_question.id
left join missions_answertext on missions_answer.id = missions_answertext.answer_ptr_id
left join missions_answerselectmultiple on missions_answer.id = missions_answerselectmultiple.answer_ptr_id
left join missions_answerinteger on missions_answer.id = missions_answerinteger.answer_ptr_id
left join missions_response on missions_answer.response_id = missions_response.id
left join crm_player on missions_response.player_id = crm_player.id
LEFT JOIN crm_user ON crm_player.user_id = crm_user.id
where missions_answer.response_id = '71788176'
group by missions_answer.response_id, crm_player.document,missions_answertext.body,
missions_question.label,
missions_answerselectmultiple.body ,
missions_answerinteger.body,
crm_user.first_name,
crm_user.last_name
这是我目前拥有的:
+ response + document + label + bill # + product + answer
- 71788176 - 79907201 - bill # - 26899 - -
- 71788176 - 79907201 - amount - - - 1
- 71788176 - 79907201 - product - - {"name": "Shoes"} -
- 71788176 - 79907201 - price - - - 25.99
这就是我要找的:
+ response + document + bill # + product + amount + price
- 71788176 - 79907201 - 26899 - shoes - 1 - 25.99
我一直在尝试使用crosstab
,但我仍然无法找到它,提前感谢任何提示或帮助。
解决方案
从您当前的状态,您可以使用以下FILTER
子句简单地进行数据透视:
SELECT
response,
document,
MAX(bill) FILTER (WHERE label = 'bill') as bill,
MAX(answer) FILTER (WHERE label = 'amount') as amount,
MAX(product) FILTER (WHERE label = 'product') as product,
MAX(answer) FILTER (WHERE label = 'price') as price
FROM t
GROUP BY response, document
我不太确定,你原来的桌子是什么样子的。如果它更像这样:
response | document | label | value
-------: | -------: | :------ | :----
71788176 | 79907201 | bill | 26899
71788176 | 79907201 | amount | 1
71788176 | 79907201 | product | shoes
71788176 | 79907201 | price | 25.99
然后你可以像这样修改查询:
SELECT
response,
document,
MAX(value) FILTER (WHERE label = 'bill') as bill,
MAX(value) FILTER (WHERE label = 'amount') as amount,
MAX(value) FILTER (WHERE label = 'product') as product,
MAX(value) FILTER (WHERE label = 'price') as price
FROM t
GROUP BY response, document
编辑:将 JSON 值添加到产品列:
变体 1:您可以简单地将类型json
转换为 type text
:
MAX(product::text) FILTER (WHERE label = 'product') as product,
变体 2:您从"name"
属性中读取值:
MAX(product ->> 'name') FILTER (WHERE label = 'product') as product,
推荐阅读
- python - 如何将元组值添加到值也是 nd.array 的字典的每个键中?
- java - 当它从java中的内部“for”循环“中断”时它会“更新”吗?
- for-loop - for-range 结构与 Go 中的闭包有何关系?
- html - 在 Bootstrap 4.1 container-fluid > row > col 中间垂直对齐 div
- python - 将多个文件合并为若干个文件
- angular - 在 Angular Nrwl Nx 中创建组件作为库
- javascript - 使用 jQuery 发送 AJAX 请求时,如何省略数据对象中的参数?
- c++ - 是否有函数/WinAPI 以不区分大小写的语言方式判断一个字符串是否以另一个字符串开头?
- java - 为什么当 GC 可以释放所有连接时需要 finalize
- arrays - 长度为 K 的 N 个整数的神奇数组 A