OK ! this is the full query sql.
SELECT
t2.*,
t1.AbnormalCount,
COUNT(*) AS TotalCount,
(1 - t1.AbnormalCount / COUNT(*)) AS NormalRate
FROM
(SELECT
*
FROM
`FlightOnTime`.`FlightOnTime`
WHERE
(`FlightDate` BETWEEN '2016-04-01' AND '2016-04-14')
AND `DepAP` IN ('ZGSZ' , 'ZGGG')
AND `Reg` IN ('B6020' , 'B6021')
ORDER BY `FlightDate` DESC) t2,
(SELECT
FlightNo, DepAP, ArrAP, COUNT(*) AS AbnormalCount
FROM
`FlightOnTime`.`FlightOnTime`
WHERE
(`FlightDate` BETWEEN '2016-04-01' AND '2016-04-14')
AND `normalFlag` = - 1
AND `DepAP` IN ('ZGSZ' , 'ZGGG')
AND `Reg` IN ('B6020' , 'B6021')
GROUP BY `FlightNo` , `DepAP`) t1
WHERE
t2.`FlightNo` = t1.`FlightNo`
AND t2.`DepAP` = t1.`DepAP`
AND t2.`ArrAP` = t1.`ArrAP`
GROUP BY t2.`FlightNo` , t2.`DepAP`
ORDER BY `NormalRate` , t2.`FlightNo`
this sql run in mysql 5.6 with EXPLAIN below
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL 194552 Using temporary; Using filesort
1 PRIMARY <derived3> ref <auto_key0> <auto_key0> 60 t2.FlightNo,t2.DepAP,t2.ArrAP 10
3 DERIVED FlightOnTime ALL 194552 Using where; Using temporary; Using filesort
2 DERIVED FlightOnTime ALL 194552 Using where; Using filesort
AND run in mysql 5.7 , EXPLAIN result is
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY FlightOnTime ALL 187430 2.78 Using where; Using temporary; Using filesort
1 PRIMARY <derived3> ref <auto_key0> <auto_key0> 60 flightontime.FlightOnTime.FlightNo,flightontime.FlightOnTime.DepAP,flightontime.FlightOnTime.ArrAP 10 100.00
3 DERIVED FlightOnTime ALL 187430 0.28 Using where; Using temporary; Using filesort
you can see , the 'ORDER BY `Flightdate` DESC' in mysql 5.7 has been ignored .
but in mysql 5.6 , the t2 has been sort correcttly .
so , the problem is not about my query , but why in 5.7 , the ORDER BY will be ignored and how to slove this problem in mysql 5.7