mysql - 按最大列值选择的每个唯一列值限制一行

Question

我正在尝试为每个 Customer 选择最新的 WorkOrder。我使用了 ORDER BY 和 GROUP BY，但返回的结果不是最新的 WorkOrder。

表结构:

WorkOrder
    id
    customer_id
    create_date
    ...

示例数据:

WorkOrder.id    WorkOrder.customer_id   WorkOrder.create_date   ...
1               1                       2012-01-01 12:00:00
2               1                       2016-06-29 12:00:00
3               2                       2015-05-05 12:00:00

期望的结果:

WorkOrder.id    WorkOrder.customer_id   WorkOrder.create_date   ...
2               1                       2016-06-29 12:00:00
3               2                       2015-05-05 12:00:00

当前代码:

$query = DB::table('WorkOrder')
    ->orderBy('WorkOrder.create_date', 'desc')
    ->groupBy('WorkOrder.customer_id');

// select * from `WorkOrder` group by `WorkOrder`.`customer_id` order by `WorkOrder`.`create_date` desc

当前结果:

WorkOrder.id    WorkOrder.customer_id   WorkOrder.create_date   ...
1               1                       2012-01-01 12:00:00
3               2                       2015-05-05 12:00:00

Answer 1

使用最大函数

SELECT WorkOrder.id, WorkOrder.customer_id, MAX(WorkOrder.create_date) FROM `WorkOrder` GROUP BY `WorkOrder`.`customer_id` ORDER BY `WorkOrder`.`create_date` DESC