SQL - 与聚合混淆

发布于 2024-10-07 18:57:56 字数 344 浏览 4 评论 0原文

我目前有这个查询：

select
  customers.emailaddress,
  MAX(orders.orderdate) as "last order"
from orders
join customers
  on orders.customerID = customers.customerID
group by customers.emailaddress

这给了我电子邮件和最后订单日期。在“Orders”表中，有一个名为“PaymentTotal”的字段，如何根据 MAX(orders.orderdate) 返回的值获取该字段？（即我试图获取每封电子邮件的最后一个订单的金额）

原文

I currently have this query:

select
  customers.emailaddress,
  MAX(orders.orderdate) as "last order"
from orders
join customers
  on orders.customerID = customers.customerID
group by customers.emailaddress

Which gives me the emails, and the last order date. With the 'Orders' table, there is a field named 'PaymentTotal', how can I get this based on the value returned by MAX(orders.orderdate)? (ie am trying to get the amount of the last order per email)

分享到QQ

分享到微博

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

发布评论

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

罪歌 2024-10-14 18:57:56

select c.EmailAddress, om.MaxOrderDate, o.PaymentTotal
from (
    select CustomerID, MAX(orders.orderdate) as MaxOrderDate
    from orders 
    group by CustomerID
) om
inner join orders o on om.CustomerID = o.CustomerID
    and om.MaxOrderDate = o.orderdate
inner join customers c on o.customerID = c.customerID

select c.EmailAddress, om.MaxOrderDate, o.PaymentTotal
from (
    select CustomerID, MAX(orders.orderdate) as MaxOrderDate
    from orders 
    group by CustomerID
) om
inner join orders o on om.CustomerID = o.CustomerID
    and om.MaxOrderDate = o.orderdate
inner join customers c on o.customerID = c.customerID

回复收藏 0 原文

~没有更多了~