php - 我需要帮助创建 SQL 查询

Question

我需要提出一个看起来像这样的报告:

有 3 个表 Agents、Sales 和 Payments。销售和付款通过代理表链接。该报告用于显示每个代理每天的总销售额。它还需要显示代理商每天支付的任何款项，并将从总销售余额中扣除，以便我们可以在任何时间点判断代理商是否欠业务，反之亦然。

我计划解决这个问题的方法是:

• 获取每个代理商每天的所有销售额(一次查询)
• 获取每个代理人每天的所有付款(一次查询)
• 对于每个销售、代理和每天检查(每个代理每天的代理付款)(循环)
• 如果找到匹配项，则获取值并用于计算累计总数
• 否则仅显示具有累计总数的适当销售数据。

我不确定是否有查询可以让我的生活更轻松一些。谢谢。

[编辑]

代理表:ID，姓名，期初余额

销售表:ID，agent_id,数量，客户ID，销售日期

付款表:ID，agent_id,数量，银行编号，付款日期，状态

[第二次编辑]

感谢 Olaf Dietsche。没有子选择的第二个查询运行了很长时间。大约 88 秒。我还尝试了第一个子选择，它在一瞬间完成，但是总和(s.amount)和总和(p.amount)保持的值是它们应该的两倍。

[第三次编辑]

一个代理可以进行多次销售和一个代理可以进行多次付款

销售和付款之间没有直接联系

Answer 1

这是第一次剪辑

select a.name, s.sale_date, sum(s.amount), sum(p.amount),
       (select sum(amount)
        from sales
        where agent_id = a.id and sale_date <= s.sale_date) as cum_sales,
       (select sum(amount)
        from payments
        where agent_id = a.id and payment_date <= s.sale_date) as cum_payments
from agents a
join sales s on s.agent_id = a.id
join payments p on p.agent_id = a.id
group by s.agent_id, s.sale_date
order by s.agent_id, s.sale_date

累计余额为opening_balance - cum_sales + cum_payments。

SQL Fiddle

这是一个没有子选择的选择。这可能更快

select a.name, s.sale_date, sum(s.amount), sum(p.amount),
       sum(cs.amount) as cum_sales,
       sum(cp.amount) as cum_payments
from agents a
join sales s on s.agent_id = a.id
join payments p on p.agent_id = a.id
join sales cs on cs.agent_id = a.id and cs.sale_date <= s.sale_date
join payments cp on cp.agent_id = a.id and cp.payment_date <= s.sale_date
group by s.agent_id, s.sale_date
order by s.agent_id, s.sale_date

SQL Fiddle

两者都未经测试，因为我没有相关数据。