如何使用GROUP BY AND ROLLUP对MySQL查询的输出重新排序？

debugcn 发表于 Dev

基思·德·凯泽（Keith D Kaiser）

有人问过类似的问题，但没有一个解决我的问题。

我有以下MySQL查询，

foreach($db_found->query("
SELECT   netID
        ,activity
        ,YEAR(logdate)                   as Year
        ,DATE_FORMAT(logdate, '%M')      as Month
        ,WEEK(logdate,0)                 as Week
        ,DATE(logdate)                   as Date
        ,DATE_FORMAT(logdate, '%m')      as mnum
        ,COUNT( callsign )               as Logins
        ,COUNT( distinct callsign )      as Stations
        ,SEC_TO_TIME(sum(timeonduty))    as TOD
    FROM  NetLog
   WHERE netID <> 0
     AND logdate >= now()-interval 2 month
     AND activity NOT LIKE '%TEST%'
GROUP BY  Week,  netID WITH ROLLUP
") as $rpt { echo stuff here }

它产生我需要的确切输出，但有一个例外，即排序顺序。我想做一个“按周DESC排序”，但是这样做的时候我得到的只是错误。我已经在“分组依据”之前和之后用“顺序依据”进行了尝试。我也尝试使用CUBE而不是ROLLUP查询。我要做的就是列出相同的输出，但按相反的顺序排列。

古尔温德·辛格（Gurwinder Singh）

将您的查询放在这样的子查询中并排序。

Select * from (
    SELECT   netID
        ,activity
        ,YEAR(logdate)                   as Year
        ,DATE_FORMAT(logdate, '%M')      as Month
        ,WEEK(logdate,0)                 as Week
        ,DATE(logdate)                   as Date
        ,DATE_FORMAT(logdate, '%m')      as mnum
        ,COUNT( callsign )               as Logins
        ,COUNT( distinct callsign )      as Stations
        ,SEC_TO_TIME(sum(timeonduty))    as TOD
    FROM  NetLog
   WHERE netID <> 0
     AND logdate >= now()-interval 2 month
     AND activity NOT LIKE '%TEST%'
GROUP BY  Week,  netID WITH ROLLUP
) as t order by week desc;