我有兩個用于銀行資料庫的表：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|
uj5u.com熱心網友回復：
類似的東西
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/313933.html
標籤：sql PostgreSQL
上一篇：有什么方法可以加快PostgreSQL中的“foo%”查詢速度？
下一篇：SQL計算視圖中的時間差異