MySQL 按日期分组 DATE_FORMAT() 函数

Question

我有以下查询:

SELECT DATE_FORMAT(order_completed, "%c/%Y") AS Month, COUNT(*) AS Total_Transactions, SUM(order_total_grand) as Total_Spend FROM `shop_orders` GROUP BY DATE_FORMAT(order_completed, "%c/%Y") DESC

输出下表:

如何正确地按日期对表格进行排序？

Answer 1

不要按日期的字符串版本排序。按原始日期排序:

SELECT DATE_FORMAT(order_completed, "%c/%Y") AS Month, COUNT(*) AS Total_Transactions,
       SUM(order_total_grand) as Total_Spend
FROM shop_orders
GROUP BY DATE_FORMAT(order_completed, "%c/%Y")
ORDER BY MIN(order_completed) DESC;

字符串按字符串排序，而不是日期。