SQL:通过多个表获取客户的总份额

Question

我有3个表customer (cid，name，phone)和transactions (cid(参考)，fundid，date，shares)和fund (fundid，fund_name)。

我正在尝试编写一个sql查询，以获得每个基金的每个客户的股票总数。

以下是插入示例：

INSERT INTO CUSTOMER(1, 'Alex', '123456678');

INSERT INTO CUSTOMER(2, 'Bill', '6323450236');

INSERT INTO CUSTOMER(3, 'Marie', '8568289912');



INSERT INTO FUND (1, 'Docotel');

INSERT INTO FUND (2, 'Armen');

INSERT INTO FUND (3, 'TD');




INSERT INTO TRANSACTIONS(1, 2, '2010-2-12', 234); (means shares bought)

INSERT INTO TRANSACTIONS(3, 1, '2010-4-2', 192);

INSERT INTO TRANSACTIONS(1, 2, '2010-4-22', -45); (the '-' means shares sold) 

INSERT INTO TRANSACTIONS(1, 3, '2010-4-26', 220);

INSERT INTO TRANSACTIONS(3, 2, '2010-7-21', 170);

我希望sql结果看起来像这样：

Name| Fund_Name | Total_Shares |

Alex Docotel 189

Alex TD 220

Marie Docotel 192

Marie Armen 170

谢谢

Answer 1

试试这个：

SELECT customer.name, fund.fund_name, T1.total_shares
FROM
(
    SELECT cid, fundid, SUM(shares) AS total_shares
    FROM transactions
    GROUP BY cid, fundid
) T1
JOIN customer ON T1.cid = customer.cid
JOIN fund ON T1.fundid = fund.fundid
ORDER BY customer.name