MySQL ORDER BY排序异常：ROLLUP总计行位置错误的SQL修复方案

问题背景 #

我尝试在单条SQL语句中实现数据统计，同时输出对应位置的小计与总计。当前的SQL语句如下：

SELECT Date, DOW, Week, Year, logdate, Month, monum, netID, Logins, creds, newb, netCnt, TOD, netCnt, activity 
FROM (SELECT logdate ,activity ,DATE( logdate ) AS Date ,DAYOFWEEK( logdate ) AS DOW ,WEEK( logdate,0 ) AS Week ,YEAR( logdate ) AS Year ,DATE_FORMAT( logdate, '%M' ) AS Month ,DATE_FORMAT( logdate, '%m' ) AS monum ,CONVERT( netID,UNSIGNED INTEGER ) AS netID ,COUNT( callsign ) AS Logins ,COUNT( IF(creds <> '',1,NULL) ) AS creds ,COUNT( IF(comments LIKE '%first log in%',1,NULL) ) AS newb ,count( DISTINCT netID ) AS netCnt ,SUM( DISTINCT netID) AS allCnt ,SEC_TO_TIME( SUM(timeonduty) ) AS TOD 
FROM NetLog 
WHERE netID <> 0 AND activity NOT LIKE '%TEST%' AND netcall LIKE '%W0KCN%' AND substr(logdate,1,4) = 2017 
GROUP BY Month, netID WITH ROLLUP ) AS t 
ORDER BY t.logdate , logins

输出结果中各月份排序正常，但十月的总计行被排在十月数据之前，而非预期的所有月份末尾（十二月之后）。请问能否通过SQL控制解决这个问题？如果可以，该如何修复？

问题原因 #

出现这个问题的核心是ORDER BY t.logdate的逻辑：ROLLUP生成的总计行中，logdate字段会继承分组内的第一条记录值（比如十月分组的第一个logdate），导致总计行被错误地插入到对应月份的前面。此外，ROLLUP生成的总计行中Month和monum字段为NULL，默认排序时NULL会排在非NULL值之前，也会打乱预期的位置。

解决方案 #

我们可以通过添加辅助排序字段、调整排序规则来修复这个问题，确保小计行在对应月份明细之后，总计行在所有月份最后。修改后的SQL语句如下：

SELECT 
  Date, DOW, Week, Year, logdate, Month, monum, netID, Logins, creds, newb, netCnt, TOD, activity 
FROM (
  SELECT 
    logdate,
    activity,
    DATE(logdate) AS Date,
    DAYOFWEEK(logdate) AS DOW,
    WEEK(logdate, 0) AS Week,
    YEAR(logdate) AS Year,
    DATE_FORMAT(logdate, '%M') AS Month,
    DATE_FORMAT(logdate, '%m') AS monum,
    CONVERT(netID, UNSIGNED INTEGER) AS netID,
    COUNT(callsign) AS Logins,
    COUNT(IF(creds <> '', 1, NULL)) AS creds,
    COUNT(IF(comments LIKE '%first log in%', 1, NULL)) AS newb,
    COUNT(DISTINCT netID) AS netCnt,
    SUM(DISTINCT netID) AS allCnt,
    SEC_TO_TIME(SUM(timeonduty)) AS TOD,
    -- 辅助排序字段：将总计行的月份标记为13，确保排在所有自然月份之后
    CAST(COALESCE(monum, '13') AS UNSIGNED) AS sort_month,
    -- 区分明细行和小计行：明细行返回0，小计/总计行返回1，保证小计在对应月份明细之后
    GROUPING(netID) AS is_subtotal
  FROM NetLog 
  WHERE 
    netID <> 0 
    AND activity NOT LIKE '%TEST%' 
    AND netcall LIKE '%W0KCN%' 
    AND YEAR(logdate) = 2017 -- 替换substr为更高效的YEAR函数
  GROUP BY Month, netID WITH ROLLUP 
) AS t 
-- 按月份排序→按是否为小计行排序→最后按netID和logdate微调
ORDER BY 
  t.sort_month, 
  t.is_subtotal, 
  t.netID, 
  t.logdate;

关键说明 #

• COALESCE(monum, '13')：把总计行的monum（月份字符串）替换为'13'，转成整数后比12大，确保总计行排在12月数据之后；
• GROUPING(netID)：MySQL的GROUPING函数专门用于ROLLUP场景，在小计/总计行返回1，明细行返回0，这样同一月份内明细行在前，小计行在后；
• 用YEAR(logdate) = 2017替代substr(logdate,1,4) = 2017，更符合SQL规范且性能更优。

修改后，十月的小计行会排在十月明细行之后，所有数据的总计行会稳稳落在12月数据的最后，完全符合预期的排序逻辑。

内容的提问来源于stack exchange，提问作者Keith D Kaiser