mysql - SQL 连接查询以返回总和和最近的条目

Question

我试图在 Laravel 应用程序中组合 SQL 查询以创建关于客户、代理 和订单 的报告，并且需要一些协助。

我已经说明了我的数据库表并从下面的模式中排除了不必要的字段:

客户表

---------------------------------------------
cust_id     cust_name           
---------------------------------------------
1           Joe 
2           Allan
3           Rick
4           Bill        

代理表

每个代理都通过 cust_id 外键与客户相关:

---------------------------------------------
agent_id    agent_name  cust_id 
---------------------------------------------
1           Mike        2
2           Arnold      4
3           Lucy        1
4           Tom         3

订单表

订单表包含客户下的所有订单(由 cust_id 通过外键引用)，包括订单日期和金额:

-----------------------------------------------
ord_id      cust_id     date        amount  
-----------------------------------------------
1           1           1/1/2001    50.00
2           1           1/1/2001    20.00
3           2           2/1/2001    20.00
4           4           2/1/2001    30.00
5           3           2/1/2001    75.00
6           3           3/1/2001    35.00
7           1           4/1/2001    20.00
8           3           5/1/2001    75.00
9           2           5/1/2001    10.00

期望的查询结果:

我想建立一个查询来检索每个客户的代理名称、最近的销售日期、最近的销售金额和销售总额(按客户名称排序):

------------------------------------------------------------------
custName    agentName   LastSaleDate    LastSaleAmt     TotalSales  
------------------------------------------------------------------
Allan       Mike        10.00           5/1/2001        30.00
Bill        Arnold      30.00           2/1/2001        30.00           
Joe         Lucy        20.00           4/1/2001        90.00
Rick        Tom         75.00           5/1/2001        185.00

现状:

到目前为止，我已经创建了所有 Laravel 模型并设置了 Eloquent 关系，但怀疑上面的查询应该使用常规 SQL 而不是 ORM 来完成。

使用 SQL，我可以检索客户名称、代理名称和总销售额，但不能检索上次销售日期和上次销售金额:

SELECT customers.name, agents.name, SUM(orders.amount) FROM customers
LEFT JOIN agents
ON customers.id=agents.cust_id
LEFT JOIN orders
ON customers.id=orders.cust_id
GROUP BY customers.id, agents.name
ORDER BY customers.name ASC

如何扩展此查询以包含最近的销售日期和金额？

感谢任何帮助!

Answer 1

这个想法是聚合以获得总数和最后的销售日期。然后在orders信息中加入回来得到最后的金额:

select oca.name, oca.agentname, oca.sales, oca.lastSaleDate,
       o.amount as lastSaleAmount
from (select c.id, c.name, a.name as agentname, sum(o.amount) as sales,
             max(o.date) as lastSaleDate
      from orders o join
           customers c
           on o.cust_id = c.id join
           agents a
           on c.id = a.cust_id
      group by c.id, c.name, a.name
     ) oca join
     orders o
     on o.cust_id = oca.cust_id and oca.lastSaleDate = o.date