mysql - 将 Rollup 与 ORDER BY 结合使用时出现的问题

Question

我有一个疑问:

SELECT IFnull(t.diapason,'total') as diapason, COUNT(distinct user_id) AS 
'number_of_users'
FROM (SELECT p.user_id, p.amount as total, CASE  
    when amount<=100 then '0-100' 
when amount>100 and amount<=150 then '100-150' 
when amount>150 then '>150 +' END AS diapason
    FROM 
        (SELECT payments.user_id, SUM(amount) AS amount 
       FROM payments INNER JOIN (SELECT DISTINCT user_id FROM activity where 
login_time between '2018-04-12' and '2018-04-18') a ON payments.user_id = 
a.user_id
             GROUP BY payments.user_id) p) t
GROUP BY diapason WITH ROLLUP
 ORDER BY number_of_users desc;

如果我进行此查询，我会收到消息:

ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

但是如果我在没有 ORDER BY 的情况下男性它 - 它会起作用。但我需要按顺序排列结果。

我应该做什么？

Answer 1

使用子查询进行排序

  select t1.* from   
(
SELECT IFnull(t.diapason,'total') as diapason, COUNT(distinct user_id) AS 
    'number_of_users'
    FROM (SELECT p.user_id, p.amount as total, CASE  
        when amount<=100 then '0-100' 
    when amount>100 and amount<=150 then '100-150' 
    when amount>150 then '>150 +' END AS diapason
        FROM 
            (SELECT payments.user_id, SUM(amount) AS amount 
           FROM payments INNER JOIN (SELECT DISTINCT user_id FROM activity where 
    login_time between '2018-04-12' and '2018-04-18') a ON payments.user_id = 
    a.user_id
                 GROUP BY payments.user_id) p) t
    GROUP BY diapason WITH ROLLUP
) as t1 ORDER BY t1.number_of_users desc;