在日期时间列上使用SQL Group By Order By Descending？

Question

我在MySQL数据库中有一个名为services的表格，包含以下列和行：

`id`    `service_rendered`  `created_at`
 1       repair              2016-11-19 14:40:56
 2       install             2016-11-19 14:40:58
 3       repair              2016-11-19 14:44:27
 4       install             2016-11-19 14:50:35

我正在尝试计算服务的数量以及最后一次渲染的日期/时间。列created_at是一个datetime类型。

我运行的SQL语句是：

SELECT COUNT(`service_rendered`) as `count_service`, `service_rendered`, `created_at` as `last_rendered`
FROM services
WHERE `id` IN (1,2,3)
GROUP BY `service_rendered`
ORDER BY `created_at` DESC

但我理解的是：

`count_service`    `service_rendered`  `last_rendered`
 2                  repair              2016-11-19 14:40:56
 1                  install             2016-11-19 14:40:58

我该如何编写SQL语句才能在维修时获得2016-11-19 14:44:27？

- Jaime Dolor jr.

Answer 1

您可以使用以下解决方案，使用 MAX 函数在 created_at 列上进行操作：

SELECT COUNT(`service_rendered`) AS `count_service`, `service_rendered`, MAX(`created_at`) AS `last_rendered`
FROM services
WHERE `id` IN (1,2,3)
GROUP BY `service_rendered`
ORDER BY `last_rendered` DESC

演示： http://sqlfiddle.com/#!9/3906b7/2

您想按created_at DESC排序吗？

您需要使用列last_rendered的别名，将ORDER BY created DESC替换为ORDER BY last_rendered DESC

- Sebastian Brosch

Answer 2

你需要选择 max(created_date) 而不是 created_date 作为 last_rendered。 然后你的查询将变成：

select 
    count(service_rendered) as count_service,
    service_rendered,
    max(created_at) as last_rendered
from
    services
group by service_rendered
order by created_at desc

- kaushik karan

---

你错过了 OP 添加的 WHERE id IN (1,2,3)。 - Jibin Balachandran