- 积分
- 7
- UID
- 21624
- 阅读权限
- 10
- 注册时间
- 2016-4-15
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
本帖最后由 pandaYipDev 于 2016-4-15 20:16 编辑
需求是这样的:根据FlightNo,DepAP,ArrAP分组,查出FlightDate区间内符合DEPAP Reg 条件的数据总数(TotalCount)以及 加上条件(`normalFlag` = -1 ) 之后的数量(AbnormalCount),以及最后一个FlightDate的整条数据。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 (SOMETHING) AND `Reg` IN (SOMETHING) 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 (SOMETHING)AND `Reg` IN (SOMETHING) 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`
这个在我的mysql 5.6.17 (随着wampserver一起安装的)上运行时正常的,
然后我迁移到一个利用mysqlinstaller安装的5.7.11是,别名T2表中的ORDER BY `FlightDate` DESC是不起作用的。
用workbench的explan图标如下
(稍后补上)
,我不知道是我的设置问题还是mysql 的版本问题,大神求解,如果需要其他的信息,我会稍后补上
|
|