sql - 如何将表中的行聚合到一个字符串?
问题描述
我有两个银行数据库表:clients
和accounts
.
clients:
|surname|city_name|client_id|
|Zubenko|Moscow | 1 |
|Dorin |Stavropol| 2 |
|Brylska|Moscow | 3 |
accounts:
|acc_id|client_id|currency_type|available_amount|
| 1 | 1 | RUB | 34012 |
| 2 | 1 | USD | 5699 |
| 3 | 3 | GBP | 140000 |
| 4 | 3 | GBP | -1133242 |
| 5 | 2 | RUB | -130322 |
| 6 | 2 | EUR | -19922 |
我需要的是制作一个函数,其结果是一个表格。函数必须返回拥有多个账户 ( acc_id
) 且至少有一个账户为负数的客户列表available_amount
。返回的表需要包含三列client_id
:surname
和accounts
。最后一列是下一个格式的字符串(对于 each client_id
):
acc_id_1 (currency_type_1): available_amount_1; acc_id_2 (currency_type_2): available_amount_2; ...
所以对于我的示例答案是:
|client_id|surname|accounts|
| 2 | Dorin |5 (RUB): -130322; 6 (EUR): -19922|
| 3 |Brylska|3 (GBP): 140000; 4 (GBP): -1133242|
我的尝试是:
SELECT array_to_string(array_agg(acc_id), ', ')
from
(SELECT client_id, surname, acc_id, currency_type, available_amount
FROM
( SELECT a.client_id, a.acc_id, a.currency_type,a.available_amount
FROM accounts a, accounts b
where a.available_amount <0
GROUP BY a.client_id, a.acc_id,a.currency_type, a.available_amount
HAVING COUNT(a.acc_id) > 1) acc NATURAL JOIN clients) boo
group by client_id
哪个返回
|array_to_string|
|5, 6|
|4|
所以它似乎过于复杂而不优雅。此外,这个查询不计入正数available_amount
(只有负数),也不能解决我的任务(我需要一张表)。我怎样才能解决我的任务?
升级版:
我做了更优雅的查询,但仍然未知如何以字符串格式制作最后一列,我描述了:
select client_id, array_to_string(array_agg(acc_id), ', ') accounts
from accounts
where client_id in (SELECT distinct a.client_id
FROM accounts a, accounts b
where a.available_amount <0)
group by client_id
返回
|client_id|accounts|
|2|5, 6|
|3|3, 4|
解决方案
类似的东西
Select client_id,surname, acc_list
from
(Select
client_id,surname,
count(*) as all_count,
Sum(case when available_amount<0 then 1 else 0 end) as neg_count,
String_agg (..) as acc_list
from clients c join amounts a on c.clinet_id=a.client_id group by 1,2 ) t
where all_count>1 and neg_count>1
推荐阅读
- http - 日志分析查询汇总 dau 明智的所有 http 错误计数
- r - 如何正确描述 5D 张量?(keras, r)
- python - python中svg的非线性变换
- dictionary - Leafletjs 地图交互式 Choropleth 地图
- node.js - Swagger 发送此错误:TypeError: NetworkError when trying to fetch resource in Fastify NodeJS
- python - RNN:了解连接层
- java - java LRU缓存:带有时间戳的LinkedHashMap?
- java - 有没有办法将 Java8 Optional 返回值与在 null 上打印消息相结合?
- html - JPA、nativeQuery 的值如上图所示
- dropdown - 下拉列的 JPA 实体(如何填充下拉列表)