我如何获得每个客户支付的总金额减去收取的金额(em_paid_to
(?
表客户
cust_id INT
f_name VARCHAR
l_name VARCHAR
email VARCHAR
c_limit INT
表事务
id INT
em_paid_by VARCHAR
em_paid_to VARCHAR
amount INT
trans_date DATE
我已经尝试过这样做来获得每个客户支付的总额,但它不起作用:
SELECT C.F_NAME, C.L_NAME, COUNT(T.EM_PAID_BY), SUM(T.AMOUNT)
FROM CUSTOMER C
JOIN TRANSACTION T ON C.EMAIL = T.EM_PAID_BY;
。这要得到每个客户收集的总数,仍然是相同的错误,我需要得到两个结果之间的差异。
SELECT C.F_NAME, C.L_NAME, COUNT(T.EM_PAID_TO), SUM(T.AMOUNT)
FROM CUSTOMER C
JOIN TRANSACTION T ON C.EMAIL = T.EM_PAID_TO;
我希望得到的是像这个老麦当劳 oldmcdonald@gmail.com 2000即(2000 + 4000 + 1000( - (2000 + 3000( = 2000
现有查询会生成错误,因为它们使用聚合函数(SUM()
、COUNT()
(而不使用GROUP BY
子句来列出所有非聚合列。
为了解决您的需求,一种解决方案是使用条件聚合:
- 恢复客户电子邮件出现在
em_paid_to
或em_paid_by
子句中的所有交易 - 按客户分组(明智的选择是将客户 ID 添加到
GROUP BY
子句中,即使它不是结果的一部分( - 执行条件计数和求和,具体取决于记录是在
em_paid_to
上匹配还是在em_paid_by
上匹配
以下查询提供了详细信息(付款人数和付款人数、支付金额和余额(,你可以选择与你相关的内容:
SELECT
c.f_name,
c.l_name,
SUM(c.email = t.em_paid_by) count_paid_by,
SUM(c.email = t.em_paid_to) count_paid_to,
SUM(CASE WHEN c.email = t.em_paid_by THEN t.amound ELSE 0 END) total_paid_by,
SUM(CASE WHEN c.email = t.em_paid_to THEN t.amound ELSE 0 END) total_paid_to,
SUM(CASE WHEN c.email = t.em_paid_by THEN t.amound ELSE -1 * t.amount END) balance
FROM
customer c
INNER JOIN transaction t
ON c.email IN (t.em_paid_by, t.em_paid_to)
GROUP BY
c.cust_id,
c.f_name,
c.l_name
;
我会对数据进行透视并聚合:
select t.email, c.fname, c.lname, sum(t.amount)
from ((select em_paid_by as email, -amount as amount
from transaction t
) union all
(select em_paid_to, amount
from transaction t
)
) t
group by email;
您可以联接到customer
表以获取其他客户信息:
select email, sum(amount)
from cusomer c join
((select em_paid_by as email, -amount as amount
from transaction t
) union all
(select em_paid_to, amount
from transaction t
)
) t
on c.email = t.email
group by t.email, c.fname, c.lname;
我会在 SELECT 子句中使用相关的子查询:
select c.*,
coalesce((
select sum(amount)
from transaction t
where t.em_paid_by = c.email
), 0)
-
coalesce((
select sum(amount)
from transaction t
where t.em_paid_to = c.email
), 0) as paid_balance
from customer c
如果你想要更多信息,比如交易计数,我会在 FROM 子句中使用子查询:
select c.*,
p.cnt_paid,
r.cnt_received
coalesce(p.sum_paid, 0) as sum_paid,
coalesce(r.sum_received, 0) as sum_received,
coalesce(p.sum_paid, 0) - coalesce(r.sum_received, 0) as paid_balance,
p.cnt_paid + r.cnt_received as total_transactions
from customer c
left join (
select em_paid_by as email, sum(amount) as sum_paid, count(*) as cnt_paid
from transaction
group by em_paid_by
) p on p.email = c.email
left join (
select em_paid_to as email, sum(amount) as sum_received, count(*) as cnt_received
from transaction
group by em_paid_to
) r on r.email = c.email