mysql - 需要报告客户订单的最新状态，以及每个客户的每个订单状态的计数

Question

数据库布局：

这个问题基本上总结了我需要返回的内容。

每个客户有多个订单，但我需要报告最新订单的状态。

我还需要为每个客户的订单（'Cancelled''Disputed'和'On Hold'）制作三列订单状态计数。

如果有人能指出我正确的方向，那将非常有帮助。

标签： mysqlsqlcountaggregate-functionsgreatest-n-per-group

Answer 1

您可以使用窗口函数和条件聚合：

select customernumber,
    max(case when rn = 1 then status end) as last_order_status,
    sum(status = 'Cancelled') cnt_cancelled,
    sum(status = 'Disputed' ) cnt_disputed,
    sum(status = 'On Hold'  ) cnt_on_hold
from (
    select o.*, 
        row_number() over(partition by customernumber order by orderdate desc) rn
    from orders o
) t
group by customernumber

请注意，row_number()仅在 MySQL 8.0 中可用。在早期版本中，您可以使用 correlatead 子查询来检索最新状态：

select customernumber,
    (select o1.status from orders o1 where o1.customernumber = o.customernumber order by o1.orderdate desc limit 1) as last_order_status,
    sum(status = 'Cancelled') cnt_cancelled,
    sum(status = 'Disputed' ) cnt_disputed,
    sum(status = 'On Hold'  ) cnt_on_hold
from orders o
group by customernumber