mysql 按总数和名称对组进行排序不起作用

Question

我有一个 PHP 程序，可以输出名称以及参加的相应事件以及一段时间内参加每个事件的次数。作为输出示例

Name | Run | Swim | Bike | Total

John    3     2      5      10

MySQL 查询看起来像这样:

$sql = 'SELECT 
            e.name as Leader,                                       
            SUM(CASE WHEN c.catid = 26 THEN 1 ELSE null END) as "Swim",
            SUM(CASE WHEN c.catid = 25 THEN 1 ELSE null END) as "Bike",
            SUM(CASE WHEN c.catid = 24 THEN 1 ELSE null END) as "Run",
            COUNT("Swim"+"Bike"+"Run") as total
        FROM 
            events as e   
        LEFT JOIN event_categories as c ON c.uid = e.uid
        WHERE 
            (DATE(e.event_start) BETWEEN "'.$from_date.'" and "'.$to_date.'") 
        GROUP BY Leader WITH ROLLUP;';

这很有效，但是，如果我想按“总计”降序对数据进行排序，如果我用以下内容替换最后一个 GROUP BY 行，则不会得到任何输出:

  GROUP BY total DESC, Leader WITH ROLLUP;';

这样我就可以得到一个列表，其中包含总分最高到最低的姓名，总分相同的人按字母顺序列出。我做错了什么？

Answer 1

正如评论中提到的，ORDER BY 和 ROLLUP 不能一起使用。它在这里( http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html )说明了这一点，大约在页面的一半处。为了解决这个问题，您必须在另一个查询中执行 ORDER BY，其中原始查询充当子查询:

SELECT *
FROM
    (
        SELECT 
            e.name as Leader,                                       
            SUM(CASE WHEN c.catid = 26 THEN 1 ELSE null END) as "Swim",
            SUM(CASE WHEN c.catid = 25 THEN 1 ELSE null END) as "Bike",
            SUM(CASE WHEN c.catid = 24 THEN 1 ELSE null END) as "Run",
            COUNT("Swim"+"Bike"+"Run") as total
        FROM 
            events as e   
        LEFT JOIN event_categories as c ON c.uid = e.uid
        WHERE 
            (DATE(e.event_start) BETWEEN "'.$from_date.'" and "'.$to_date.'") 
        GROUP BY Leader WITH ROLLUP     
    ) as rolldup
ORDER BY Total DESC

原始(错误)答案:

您不能将排序放入 GROUP BY 子句中。您将它们放入 ORDER BY 子句中:

$sql = 'SELECT 
        e.name as Leader,                                       
        SUM(CASE WHEN c.catid = 26 THEN 1 ELSE null END) as "Swim",
        SUM(CASE WHEN c.catid = 25 THEN 1 ELSE null END) as "Bike",
        SUM(CASE WHEN c.catid = 24 THEN 1 ELSE null END) as "Run",
        COUNT("Swim"+"Bike"+"Run") as total
    FROM 
        events as e   
    LEFT JOIN event_categories as c ON c.uid = e.uid
    WHERE 
        (DATE(e.event_start) BETWEEN "'.$from_date.'" and "'.$to_date.'") 
    GROUP BY Leader WITH ROLLUP
    ORDER BY total DESC;';