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

发布于 2024-12-09 12:46:12 字数 876 浏览 0 评论 0原文

我有 3 个表客户（cid、姓名、电话）和交易（cid（参考）、fundid、日期、股票）和基金（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

谢谢

原文

I have 3 tables customer(cid, name, phone) and transactions (cid (reference), fundid, date, shares) and fund (fundid, fund_name).

I am trying to write an sql query that would get me the total number of shares for each customer for each fund.

Here are the sample inserts:

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);

I want the sql result to look something like this:

Name| Fund_Name | Total_Shares |

Alex Docotel 189

Alex TD 220

Marie Docotel 192

Marie Armen 170

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

把梦留给海 2024-12-16 12:46:12

试试这个：

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

Try this:

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

回复收藏 0 原文

~没有更多了~