原ORACLE语句:
select*from(select BEGINDATE,BEGINTIME from uf_PROJECT_CTMAIN where ennmcd=7632and INSPECT_TYPE ='巡查'
and ceil((To_date(endtime ,'hh24-mi-ss')- To_date(begintime ,'hh24-mi-ss'))*24*60)>=5
order by BEGINDATE desc, BEGINTIME desc)where rownum<=1order by rownum asc
迁移到瀚⾼数据库后,发现条数限制变得⽆效,因为瀚⾼没有rownum关键字,虽然不报错,但是也不会执⾏。研究后发现瀚⾼的条数是⽤row_number()的:
SELECT ROW_NUMBER()OVER(PARTITION BY DTYPE,STCD ORDER BY DT DESC,TM DESC) rn,t.*FROM t WHERE rn =1
改造后如下:
select*from(
select BEGINDATE,BEGINTIME , ROW_NUMBER()OVER(PARTITION BY ennmcd ORDER BY BEGINDATE DESC,BEGINTIME DESC) rn
from uf_PROJECT_CTMAIN where ennmcd=7632and INSPECT_TYPE ='巡查'
and ceil((To_date(endtime ,'hh24-mi-ss')- To_date(begintime ,'hh24-mi-ss'))*24*60)>=5
)where rn=1
成功
网站声明:如果转载,请联系本站管理员。否则一切后果自行承担。
加入交流群
请使用微信扫一扫!