出现问题原因:
项目中需要查询出排行前10的api调用量,使用的这条sql
错误示范:
select APP_NAME,API_NAME,TIMES
(select API_Key_ID,APP_ID,count(API_ID)as TIMES
from sjez_api_call_log group by APP_ID,API_KEY_ID)a
left join sjez_app_control_list c on a.app_id =c.id
left join sjez_api_data_list b on a.api_key_id = b.id
where ROWNUM <11
ODER BY TIMES DESC
经过一步步查看,发现上述的sql中rownum不是有序的,所以导致查出来的rownum<11不生效,后来查询很多信息,似乎是里面的那条sql的goup by 打乱了rownum顺序,导致查出来的rownum是乱序的,所以必须再次重新使rownum有序,就要再套用一次select *
正确示范:
select rownum, t1.* from
(select APP_NAME,API_NAME,TIMES
(select API_Key_ID,APP_ID,count(API_ID)AsTIMES from sjez_api_call_log group by APP_ID,API_KEY_ID)a
left join sjez_app_control_list c on a.app_id =c.id
left join sjez_api_data_list b on a.api_key_id = b.id
ODER BY TIMES DESC) t1
where ROWNUM <10