如何关联Customers与Operations表并汇总Withdraw和Deposit列数据？

你当前的查询返回的是单条操作记录的明细，要得到汇总结果，只需要用聚合函数对取款（Withdraw）和存款（Deposit）金额求和，再按客户维度分组即可。

修改后的存储过程代码如下：

select 
    a.customer_id, 
    a.customer_fullname, 
    SUM(b.amount_withdraw) as total_withdraw,  -- 汇总该客户总取款金额
    SUM(b.amount_deposit) as total_deposit     -- 汇总该客户总存款金额
from 
    Operations b 
join 
    Customers a on b.customer_id2 = a.customer_id
where  
    a.customer_id = 19
group by 
    a.customer_id, a.customer_fullname  -- 按客户分组，确保每个客户仅返回一行汇总数据

补充说明： #

• 如果需要按日期维度（如每日/每月）细分汇总，只需把entry_date加入查询和分组条件即可，示例：

select 
    a.customer_id, 
    a.customer_fullname, 
    DATE(b.entry_date) as entry_date,  -- 截断日期到年月日维度
    SUM(b.amount_withdraw) as total_withdraw,
    SUM(b.amount_deposit) as total_deposit
from 
    Operations b 
join 
    Customers a on b.customer_id2 = a.customer_id
where  
    a.customer_id = 19
group by 
    a.customer_id, a.customer_fullname, DATE(b.entry_date)

• 若需要同时支持明细查询和汇总查询，可以在存储过程中添加一个参数（比如@IsSummary bit），通过条件分支执行不同逻辑，无需拆分两个存储过程。

内容的提问来源于stack exchange，提问作者Salem Gogah