MYSQL GROUP BY/ORDER BY 混淆

Question

我正在尝试对结果进行排序，但我的 order by 语句被忽略...我理解原因，但不知道如何解决它。这是我的代码:

SELECT 
    result.station_id as 'Station ID',
    result.date as 'Date',
    result.id as 'ID',
    COALESCE(sum(if(result.parameter = '1', result.result, NULL)),'-') as 'RESULT A)',
    COALESCE(sum(if(result.parameter = '2', result.result, NULL)),'-') as 'RESULT B)',
FROM (  
    SELECT
        event.station_id,
        event.date,
        event.id,
        e_result.result
    FROM event
    Inner Join e_result ON e_result.id = event.id
    WHERE 
         (event.station_id =  '0001') AND `devent`.`date` >= '1999-02-08' AND `event`.`date` <= '2011-12-20') as result 
GROUP BY by result.id
ORDER BY 
     result.station_id ASC, 
     result.date DESC

它正在正确分组，但之后没有对结果进行排序...它似乎正在对分组进行排序。我需要在分组完成后对整个结果集(不是每个分组)进行排序

示例输出:

Station Date        ID          Result A    Result B
20      7/6/2009    g003        -           3
12      2/8/1999    g000        19.2        -
12      2/8/1999    g001        19.9        -
12      2/14/1999   g002        19.1        -
17      4/9/2003    i001        22.2        4

应该是

Station Date        ID          Result A    Result B
12      2/14/1999   g002        19.1        -
12      2/8/1999    g000        19.2        -
12      2/8/1999    g001        19.9        -
17      4/9/2003    i001        22.2        4
20      7/6/2009    g003        -           3

表格:

e_result   id, parameter, result
     g002, 1, 19.1
     g000, 1, 19.2
     g001, 1, 19.9
     i001, 1, 22.2
     i001, 2, 4
     g003, 2, 3

event      station_id, date, id
     20, 7/16/2009, g003
     12, 2/8/1999, g000
     12, 2/8/1999, g001
     12, 2/14/1999, g002
     17, 4/9/2003, i001

Answer 1

我已将您的查询放入 sqlfiddle，但必须进行大量调整才能使其正常工作。

这是一个生成您想要的结果的查询:

select result.station_id as 'Station ID',
    date_format(result.date,'%c/%m/%Y') as 'Date',
    result.id as 'ID',
    if (sum(if(result.parameter = 1, result.result,0)) = 0, 
        null, 
        sum(if(result.parameter = 1, result.result,0)))  as 'RESULT A)',
    if (sum(if(result.parameter = 2, result.result,0)) = 0, 
        null, 
        sum(if(result.parameter = 2, result.result,0)))  as 'RESULT B)'
from 
      (SELECT
        event.station_id,
        event.date,
        event.id,
        e_result.result,
        e_result.parameter
      FROM event
      Inner join e_result ON e_result.id = event.id) as result
group by 
     result.station_id, result.date, result.id
order by 
     result.station_id ASC, 
     result.date DESC;

我重新设计了合并，删除了内表结果上的 where 子句，更正了“devent”拼写错误，将参数字段添加到了内表中。以及其他一些事情

这给出:

STATION ID    DATE         ID      RESULT A    RESULT B
12            2/02/1999    g002    19.1    
12            2/02/1999    g000    19.2    
12            2/02/1999    g001    19.9    
17            4/04/2003    i001    22.2        4
20            7/07/2009    g003                3

都在这里:http://sqlfiddle.com/#!2/34d87/17