SELECT中使用DATE_FORMAT导致ORDER BY排序异常的问题排查

问题 #

我正在完成大学数据库作业，需要用GROUP BY WITH ROLLUP查询2023年每月销售数据（售出总数、总销售额），但当前SQL的月份总是按字母顺序排列，而非时间顺序。尝试调整分组或排序时，触发了与sql_mode=only_full_group_by相关的错误（1140、1055），不想关闭该模式，求合规写法。

当前SQL：

SELECT date_format(_order.orderDate, '%M') AS 'Month',
       sum(orderItem.quantity) AS 'Total Items Purchased',
       sum(orderItem.unitPrice * orderItem.quantity) AS 'Total Price' 
FROM orderItem
LEFT JOIN _order ON orderItem.orderID = _order.orderID
WHERE orderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY date_format(_order.orderDate, '%M') WITH ROLLUP
ORDER BY date_format(_order.orderDate, '%M');

表结构：

CREATE TABLE IF NOT EXISTS _order(
   orderID VARCHAR(10), 
   customerID VARCHAR(10), 
   staffID VARCHAR(10), 
   orderDate DATE NOT NULL,
   deliveryDate DATE NOT NULL,
   deliveryStreet VARCHAR(50) NOT NULL,
   deliveryCounty VARCHAR(20) NOT NULL,
   deliveryEircode VARCHAR(8) NOT NULL,  
   orderStatus VARCHAR(50) NOT NULL,
   PRIMARY KEY (orderID)
);

CREATE TABLE IF NOT EXISTS orderItem(
   itemID VARCHAR(10), 
   orderID VARCHAR(10), 
   productID VARCHAR(10), 
   quantity INT NOT NULL, 
   unitPrice DECIMAL(12, 2) NOT NULL,
   PRIMARY KEY (itemID)
);

示例数据：

INSERT INTO _order (orderID, customerID, staffID, orderDate, deliveryDate, deliveryStreet, deliveryCounty, deliveryEircode, orderStatus) VALUES ('SO65301830', 'SC45398810', 'SE55900726', '2023-05-28', '2023-05-30', '0 Aberg Parkway', 'Mayo', 'A08 OY80', 'Out for Delivery');

INSERT INTO orderItem (itemID, orderID, productID, quantity, unitPrice) VALUES ('OL127', 'SO65301830', 'SP07', 4, 14.99);

解决方案 #

问题核心是按月份名称（字符串）分组/排序，导致字母序而非时间序。要兼容only_full_group_by，需基于时间维度的分组键（如YYYY-MM）来排序，同时保留月份名称用于显示。

基础修正版 #

SELECT 
    DATE_FORMAT(o.orderDate, '%M') AS 'Month',
    SUM(oi.quantity) AS 'Total Items Purchased',
    SUM(oi.unitPrice * oi.quantity) AS 'Total Price'
FROM orderItem oi
LEFT JOIN _order o ON oi.orderID = o.orderID
WHERE o.orderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY DATE_FORMAT(o.orderDate, '%Y-%m'), DATE_FORMAT(o.orderDate, '%M') WITH ROLLUP
ORDER BY 
    -- 让ROLLUP汇总行排在最后
    CASE WHEN GROUPING(DATE_FORMAT(o.orderDate, '%Y-%m')) = 1 THEN 1 ELSE 0 END,
    -- 按年-月数字格式排序，保证时间顺序
    DATE_FORMAT(o.orderDate, '%Y-%m');

优化版（汇总行显示"Total"） #

如果希望ROLLUP生成的汇总行的Month列更清晰，可以用GROUPING()函数判断并替换：

SELECT 
    CASE 
        WHEN GROUPING(DATE_FORMAT(o.orderDate, '%Y-%m')) = 1 THEN 'Total' 
        ELSE DATE_FORMAT(o.orderDate, '%M') 
    END AS 'Month',
    SUM(oi.quantity) AS 'Total Items Purchased',
    SUM(oi.unitPrice * oi.quantity) AS 'Total Price'
FROM orderItem oi
LEFT JOIN _order o ON oi.orderID = o.orderID
WHERE o.orderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY DATE_FORMAT(o.orderDate, '%Y-%m'), DATE_FORMAT(o.orderDate, '%M') WITH ROLLUP
ORDER BY 
    CASE WHEN GROUPING(DATE_FORMAT(o.orderDate, '%Y-%m')) = 1 THEN 1 ELSE 0 END,
    DATE_FORMAT(o.orderDate, '%Y-%m');

关键逻辑说明 #

• 分组调整：将%Y-%m（年-月数字格式）加入GROUP BY，作为时间维度的分组依据，同时保留%M满足only_full_group_by对SELECT字段的要求。
• 排序控制：用GROUPING()识别汇总行（返回1时为汇总行），让它排在最后；按%Y-%m排序确保月份按时间顺序排列。
• 表别名：使用oi和o简化SQL，提升可读性。

内容的提问来源于stack exchange，提问作者iGleeson