SQL - 我有一个'People'表和一个'Account'表。如何在行中列出所有人员，并为每种帐户类型设置余额列？

Question

我很难弄清楚如何为这个问题命名，所以我提前道歉。

这是我的情况：

我有一张桌子，里面只有人。我有另一个包含所有帐户的表，其中包含人员 ID、帐户类型和余额列。一个人可以有多个帐户，有不同的帐户类型，并不是每个人都拥有所有不同的帐户类型。

如何编写查询，其中每行列出一个人，并为每个"帐户类型"的余额提供一列？

理想情况下，我的查询如下所示：

PersonID | Account Type 1 Bal | Account Type 2 Bal | Account Type 3 Bal |
-------------------------------------------------------------------------
1    |         $100       |         null       |         null       |
2    |         null       |         $12        |         $1300      |
3    |         null       |         null       |         $5         |
4    |         $150       |         null       |         null       |
5    |         $65        |         $300       |         $45        |

我假设我会使用某种案例陈述，但我还没有弄清楚。另外，如果他们有多个相同的帐户类型，我想我会使用 sum() 正确吗？

谢谢。

Answer 1

这样的东西应该适用于postgres

select p.person_id,
(select sum(a.balance) from account a where a.person_id = p.person_id and a.account_type = 'Type1') type1_balance,
(select sum(a.balance) from account a where a.person_id = p.person_id and a.account_type = 'Type2') type2_balance,
(select sum(a.balance) from account a where a.person_id = p.person_id and a.account_type = 'Type3') type3_balance
from person p

SQLfiddle 示例

Answer 2

检查这是否有帮助 -

http://sqlfiddle.com/#!4/30ebb/10/0

CREATE TABLE person
("person_id" int, "name" varchar2(9))
;
INSERT ALL 
INTO person ("person_id", "name")
VALUES (1, '''Abcd''')
INTO person ("person_id", "name")
VALUES (2, '''xyz''')
INTO person ("person_id", "name")
VALUES (3, '''jjjjj''')
INTO person ("person_id", "name")
VALUES (4, '''sfds''')
INTO person ("person_id", "name")
VALUES (5, '''temp''')
SELECT * FROM dual
;
CREATE TABLE accounts
("personID" int, "accountType" int, "balance" int)
;
INSERT ALL 
INTO accounts ("personID", "accountType", "balance")
VALUES (1, 1, 100)
INTO accounts ("personID", "accountType", "balance")
VALUES (1, 2, 150)
INTO accounts ("personID", "accountType", "balance")
VALUES (2, 1, 20)
INTO accounts ("personID", "accountType", "balance")
VALUES (3, 1, 40)
INTO accounts ("personID", "accountType", "balance")
VALUES (3, 2, 440)
INTO accounts ("personID", "accountType", "balance")
VALUES (4, 1, 600)
INTO accounts ("personID", "accountType", "balance")
VALUES (5, 1, 43)
INTO accounts ("personID", "accountType", "balance")
VALUES (5, 2, 50)
SELECT * FROM dual
;

查询-

select * from (
select p."person_id", a."accountType", a."balance"
from person p, accounts a
where p."person_id" = a."personID"
)
pivot (sum("balance") for "accountType" in (1 as acc_type1_bal,2 as acc_type2_bal));

person_id   ACC_TYPE1_BAL   ACC_TYPE2_BAL
1   100 150
2   20  (null)
4   600 (null)
5   43  50
3   40  440

Answer 3

select personid
,sum(ac1bal) accounttype1bal
,sum(ac2bal) accounttype2bal
,sum(ac3bal) accounttype3bal
from (
select persontable.personid
,case when (accounttype=1) then bal end ac1bal
,case when (accounttype=2) then bal end ac2bal
,case when (accounttype=3) then bal end ac3bal
from persontable
left join accounttable on persontable.personid = accounttable.personid
)
group by personid